如何在 PostgreSQL 中使用 ALTER TABLE 向同一个表添加外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17378946/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add a foreign key constraint to same table using ALTER TABLE in PostgreSQL
提问by NickNick
To create table I use:
要创建表，我使用：
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 is a id to another category. Now I have a problem: how to cascade delete record with its children? I need to set parent_id as foreign key to cat_id. I try this:
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
But it falls with:
但它属于：
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".
回答by Igor Romanchenko
The problem you have - what would be the parent_idof a category at the top of the hierarchy?
您遇到的问题 -parent_id层次结构顶部的类别是什么？
If it will be null- it will break the NOT NULLconstratint.
如果是的话null- 它会打破NOT NULL约束。
If it will be some arbitrary number like 0- it will break the foreign key (like in your example).
如果它将是某个任意数字，例如0- 它会破坏外键（如您的示例中所示）。
The common solution - drop the NOT NULLconstratint on the parent_idand set parent_idto nullfor top categories.
常见的解决办法-掉落NOT NULL在constratintparent_id并设置parent_id到null顶级类别。
回答by wildplasser
-- 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
;
回答by CodZilla
This is quite simple.
Here the foreign key parent_idrefers to cat_id.
Here a record with parent_id=0exists but not a record with cat_id=0.
这很简单。
这里的外键parent_id是指cat_id.
这里的记录parent_id=0存在但不存在记录cat_id=0。
