如何添加“删除级联”约束？
在 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)
两个引用的表格都在下面 - 这里：
# \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)
和这里：
# \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)
而且我想知道在前一张表中添加 2 个索引是否有意义？
更新：谢谢，而且我在邮件列表中得到了建议，我可以在 1 条语句中管理它，因此无需明确启动交易：
ALTER TABLE public.scores
DROP CONSTRAINT scores_gid_fkey,
ADD CONSTRAINT scores_gid_fkey
FOREIGN KEY (gid)
REFERENCES games(gid)
ON DELETE CASCADE;
pref_scores.gid）。如果您在这些表中获得许多行，则在引用表上的删除将需要很长时间。一些数据库会自动在引用列上创建索引； PostgreSQL 把它留给你，因为在某些情况下它是不值得的。
我很确定您不能简单地将 on delete cascade 添加到现有的外键约束中。您必须先删除约束，然后添加正确的版本。在标准 SQL 中，我相信最简单的方法是
开始交易，
删除外键，
使用 on delete 级联添加外键，最后
提交事务
对要更改的每个外键重复此操作。
但是 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;
如果您不知道要删除的外键约束的名称，您可以在 pgAdminIII 中查找它（只需单击表名并查看 DDL，或者展开层次结构直到看到“约束”），或者您可以query the information schema。
select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null
根据@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;
用法：
select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');
功能：
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;
注意：这个函数不会复制初始外键的属性。它只需要外部表名/列名，删除当前键并替换为新键。
多列约束的解决方案：
SELECT
'ALTER TABLE myschema.' || cl.relname ||
' DROP CONSTRAINT ' || con.conname || ',' ||
' ADD CONSTRAINT ' || con.conname || ' ' || pg_get_constraintdef(con.oid) || ' ON DELETE CASCADE;'
FROM pg_constraint con, pg_class cl
WHERE con.contype = 'f' AND con.connamespace = 'myschema'::regnamespace::oid AND con.conrelid = cl.oid
关注公众号
不定期副业成功案例分享
NOT VALID并在单独的事务中进行验证？我对此有an unanswered question。