postgresql - 如何在 PostgreSQL 中使用 ALTER TABLE 将外键约束添加到同一个表

Question

创建表我使用:

CREATE TABLE category
(
  cat_id serial NOT NULL,
  cat_name character varying NOT NULL,
  parent_id integer NOT NULL,
  CONSTRAINT cat_id PRIMARY KEY (cat_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE category
  OWNER TO pgsql;

parent_id 是另一个类别的 ID。现在我有一个问题:如何级联删除记录及其 child ？我需要将 parent_id 设置为 cat_id 的外键。我试试这个:

  ALTER TABLE category 
ADD CONSTRAINT cat_cat_id_fkey FOREIGN KEY (parent_id)
      REFERENCES category (cat_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE

但它属于:

ERROR:  insert or update on table "category" violates foreign key constraint "cat_cat_id_fkey"
DETAIL:  Key (parent_id)=(0) is not present in table "category".

Answer 1

您遇到的问题 - 层次结构顶部类别的 parent_id 是什么？

如果它将是 null - 它将打破 NOT NULL 约束。

如果它是某个任意数字，例如 0 - 它会破坏外键(就像您的示例中一样)。

常见的解决方案 - 删除 parent_id 上的 NOT NULL 约束并将 parent_id 设置为 null 以获得顶部类别。