postgresql 如何添加“删除级联”约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10356484/
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 "on delete cascade" constraints?
提问by Alexander Farber
In PostgreSQL 8 is it possible to add ON DELETE CASCADESto the both foreign keys in the following table without dropping the latter?
在 PostgreSQL 8 中是否可以添加ON DELETE CASCADES到下表中的两个外键而不删除后者？
# \d scores
Table "public.scores"
Column | Type | Modifiers
---------+-----------------------+-----------
id | character varying(32) |
gid | integer |
money | integer | not null
quit | boolean |
last_ip | inet |
Foreign-key constraints:
"scores_gid_fkey" FOREIGN KEY (gid) REFERENCES games(gid)
"scores_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
Both referenced tables are below - here:
两个参考表都在下面 - 在这里：
# \d games
Table "public.games"
Column | Type | Modifiers
----------+-----------------------------+----------------------------------------------------------
gid | integer | not null default nextval('games_gid_seq'::regclass)
rounds | integer | not null
finished | timestamp without time zone | default now()
Indexes:
"games_pkey" PRIMARY KEY, btree (gid)
Referenced by:
TABLE "scores" CONSTRAINT "scores_gid_fkey" FOREIGN KEY (gid) REFERENCES games(gid)
And here:
和这里：
# \d users
Table "public.users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
first_name | character varying(64) |
last_name | character varying(64) |
female | boolean |
avatar | character varying(128) |
city | character varying(64) |
login | timestamp without time zone | default now()
last_ip | inet |
logout | timestamp without time zone |
vip | timestamp without time zone |
mail | character varying(254) |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "cards" CONSTRAINT "cards_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "catch" CONSTRAINT "catch_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "chat" CONSTRAINT "chat_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "game" CONSTRAINT "game_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "hand" CONSTRAINT "hand_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "luck" CONSTRAINT "luck_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "match" CONSTRAINT "match_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "misere" CONSTRAINT "misere_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "money" CONSTRAINT "money_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "pass" CONSTRAINT "pass_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "payment" CONSTRAINT "payment_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "rep" CONSTRAINT "rep_author_fkey" FOREIGN KEY (author) REFERENCES users(id)
TABLE "rep" CONSTRAINT "rep_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "scores" CONSTRAINT "scores_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "status" CONSTRAINT "status_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
And also I wonder if it makes sense to add 2 index'es to the former table?
而且我想知道将 2 个索引添加到前一个表是否有意义？
UPDATE:Thank you, and also I've got the advice at the mailing list, that I could manage it in 1 statement and thus without explicitly starting a transaction:
更新：谢谢，而且我在邮件列表中得到了建议，我可以在 1 条语句中管理它，因此无需明确启动事务：
ALTER TABLE public.scores
DROP CONSTRAINT scores_gid_fkey,
ADD CONSTRAINT scores_gid_fkey
FOREIGN KEY (gid)
REFERENCES games(gid)
ON DELETE CASCADE;
回答by Mike Sherrill 'Cat Recall'
I'm pretty sure you can't simply add on delete cascadeto an existing foreign key constraint. You have to drop the constraint first, then add the correct version. In standard SQL, I believe the easiest way to do this is to
我很确定您不能简单地添加on delete cascade到现有的外键约束中。您必须先删除约束，然后添加正确的版本。在标准 SQL 中，我相信最简单的方法是
- start a transaction,
- drop the foreign key,
- add a foreign key with
on delete cascade, and finally - commit the transaction
- 开始交易，
- 删除外键，
- 添加一个外键
on delete cascade，最后 - 提交交易
Repeat for each foreign key you want to change.
对要更改的每个外键重复此操作。
But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. For example
但是 PostgreSQL 有一个非标准扩展，它允许您在单个 SQL 语句中使用多个约束子句。例如
alter table public.scores
drop constraint scores_gid_fkey,
add constraint scores_gid_fkey
foreign key (gid)
references games(gid)
on delete cascade;
If you don't know the name of the foreign key constraint you want to drop, you can either look it up in pgAdminIII (just click the table name and look at the DDL, or expand the hierarchy until you see "Constraints"), or you can query the information schema.
如果您不知道要删除的外键约束的名称，则可以在 pgAdminIII 中查找（只需单击表名并查看 DDL，或展开层次结构直到看到“约束”），或者您可以查询信息架构。
select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null
回答by Fellow Stranger
Based off of @Mike Sherrill Cat Recall's answer, this is what worked for me:
根据@Mike Sherrill Cat Recall 的回答，这对我有用：
ALTER TABLE "Children"
DROP CONSTRAINT "Children_parentId_fkey",
ADD CONSTRAINT "Children_parentId_fkey"
FOREIGN KEY ("parentId")
REFERENCES "Parent"(id)
ON DELETE CASCADE;
回答by Daniel Garmoshka
Usage:
用法：
select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');
Function:
功能：
CREATE OR REPLACE FUNCTION
replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR)
RETURNS VARCHAR
AS $$
DECLARE constraint_name varchar;
DECLARE reftable varchar;
DECLARE refcolumn varchar;
BEGIN
SELECT tc.constraint_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'
AND tc.table_name= f_table AND kcu.column_name= f_column
INTO constraint_name, reftable, refcolumn;
EXECUTE 'alter table ' || f_table || ' drop constraint ' || constraint_name ||
', ADD CONSTRAINT ' || constraint_name || ' FOREIGN KEY (' || f_column || ') ' ||
' REFERENCES ' || reftable || '(' || refcolumn || ') ' || new_options || ';';
RETURN 'Constraint replaced: ' || constraint_name || ' (' || f_table || '.' || f_column ||
' -> ' || reftable || '.' || refcolumn || '); New options: ' || new_options;
END;
$$ LANGUAGE plpgsql;
Be aware: this function won't copyattributes of initial foreign key. It only takes foreign table name / column name, drops current key and replaceswith new one.
请注意：此函数不会复制初始外键的属性。它只需要外部表名/列名，删除当前键并替换为新键。
