如何在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。现在我有一个问题：如何级联删除记录及其子项？我需要将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".

- NickNick

---

错误信息非常清晰明了，对吧？你有一行数据的 parent_id = 0，但是没有 cat_id = 0 的数据行。 - user330315

@My-Name-Is 哇！你在关系型数据处理中没有做过树形结构吗？这是因为这里需要一个外键。 - user2511414

Answer 1

你面临的问题是，层级结构中顶部类别的parent_id应该是什么？

如果它是null，将会破坏NOT NULL约束。

如果它是像0这样的任意数字，将会破坏外键（就像你的例子中一样）。

常见的解决方案是，删除parent_id上的NOT NULL约束，并将顶级类别的parent_id设置为null。

- Ihor Romanchenko

Answer 2

-- create some fake data for testing
--
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
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)
);

INSERT INTO category(cat_name,parent_id)
SELECT 'Name_' || gs::text
        , gs % 3
FROM generate_series(0,9) gs
        ;

        -- find the records with the non-existing parents
SELECT ca.parent_id , COUNT(*)
FROM category ca
WHERE NOT EXISTS (
        SELECT *
        FROM category nx
        WHERE nx.cat_id = ca.parent_id
        )
GROUP BY ca.parent_id
        ;

        -- if all is well: proceed
        -- make parent pointer nullable
ALTER TABLE category
        ALTER COLUMN parent_id DROP NOT NULL
        ;

        -- set non-existing parent pointers to NULL
UPDATE category ca
SET parent_id = NULL
WHERE NOT EXISTS (
        SELECT *
        FROM category nx
        WHERE nx.cat_id = ca.parent_id
        )
        ;

        -- Finally, add the FK constraint
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
        ;

- wildplasser

Answer 3

这很简单。
这里的外键parent_id指向cat_id。
这里存在一个parent_id=0的记录，但不存在cat_id=0的记录。

- CodZilla