如何添加“on delete cascade”约束？

Question

如何添加“on delete cascade”约束？

postgresqlconstraintscascadecascading-deletespostgresql-8.4

247

在 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;

- Alexander Farber

1

有点离题，但我注意到您没有在引用列上创建索引（例如，pref_scores.gid）。如果这些表中有许多行，则在引用表上进行删除操作将需要很长时间。一些数据库会自动在引用列上创建索引；PostgreSQL则由您决定是否创建，因为有些情况下不值得创建。 - kgrittn

1

谢谢！我确实注意到删除需要很长时间，但不知道这是原因。 - Alexander Farber

1

在哪些情况下，外键索引不值得使用？ - Alexander Farber

@AlexanderFarber ：很好的提醒，PostgreSQL允许在单个语句中使用多个约束子句。由于我必须支持多个平台，我倾向于依赖标准SQL。我经常忘记这些有用的扩展。 - Mike Sherrill 'Cat Recall'

2

我将你的发现融入了我的答案中。（这个单一的声明也是一个单一的事务。） - Mike Sherrill 'Cat Recall'

2

@AlexanderFarber：在外键的引用列上省略索引的情况有哪些？当存在另一个不完全匹配但足够好用的索引时（例如，您可能具有用于频繁相似性搜索的三元组索引，这对于FK删除也可以使用）。当删除不频繁且可以安排在非工作时间进行时。当表中引用值经常更新时。当引用表非常小但经常更新时。异常情况经常发生，因此PostgreSQL社区更喜欢控制它而不是自动化。 - kgrittn

5个回答

53

基于 @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;

- Fellow Stranger

1

提醒下一个人，在PostgreSQL中你不必像这样手动命名约束。所以，你可以只写ADD FOREIGN KEY ("parentId") REFERENCES "Parent"(id)，然后PostgreSQL会自动为你适当地命名约束为“ChildrenId_parentId_fkey”。 - birgersp

12

使用方法：

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;

注意：此功能不会复制初始外键的属性。它只接受外键表名/列名，删除当前键，并用新键替换它。

- Daniel Garmoshka

能否将此函数创建为触发器，并在删除之前仅运行此函数？ - Craig Howell

我猜这应该是可能的，但不确定这是否是一个好主意？这样做有什么用例吗？ - Can Rau

3

多列约束条件的解决方案：

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

- damike

我不得不稍微编辑一下输出结果，但这是一个很好的开始。pg_get_constraintdef有时也会输出“set not null”和“on delete XX”，这将与第二个“on delete cascade”产生冲突。 - Renato Cron

0

解决方案用于更改多个表上的选项。

用法：

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 SETOF VARCHAR AS $$
  DECLARE reftable varchar;
  DECLARE refcolumn varchar;
  DECLARE constraint_name_tmp varchar;
BEGIN

FOR reftable, refcolumn, constraint_name_tmp IN
    SELECT kcu.table_name AS table_name, kcu.column_name AS column_name, tc.constraint_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 ccu.table_name= f_table AND ccu.column_name= f_column
    LOOP

        EXECUTE 'ALTER TABLE ' || reftable || ' DROP CONSTRAINT ' || constraint_name_tmp ||
                ', ADD CONSTRAINT ' || constraint_name_tmp || ' FOREIGN KEY (' || refcolumn || ') ' ||
                ' REFERENCES ' || f_table || '(' || f_column || ') ' || new_options || ';';

        RETURN NEXT 'Constraint replaced: ' || constraint_name_tmp || ' (' || f_table || '.' || f_column ||
                    ' -> ' || reftable || '.' || refcolumn || '); New options: ' || new_options;
    END LOOP; 
END; $$ LANGUAGE plpgsql;

感谢你的代码，Daniel

- Giovanni C

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mike Sherrill 'Cat Recall' · Accepted Answer

我相信你不能简单地向现有的外键约束中添加 on delete cascade。你必须首先删除约束，然后添加正确的版本。在标准SQL中，我认为最简单的方法是：

开始一个事务，
删除外键，
添加带有 on delete cascade 的外键，最后
提交事务

对于每个要更改的外键重复执行此操作。

但是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，或展开层次结构直到看到“Constraints”），或者您可以查询信息模式。

查询信息模式

select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null