如何添加“删除级联"约束? [英] How to add "on delete cascade" constraints?

问题描述

在 PostgreSQL 8 中是否可以将 ON DELETE CASCADES 添加到下表中的两个外键而不删除后者?

In PostgreSQL 8 is it possible to add ON DELETE CASCADES to the both foreign keys in the following table without dropping the latter?

# 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)

这里:

# 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 个索引是否有意义?

And also I wonder if it makes sense to add 2 index'es to the former table?

更新:谢谢，而且我在邮件列表中得到了建议，我可以在 1 个语句中管理它，因此无需显式启动事务:

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:

ALTER TABLE public.scores
DROP CONSTRAINT scores_gid_fkey,
ADD CONSTRAINT scores_gid_fkey
   FOREIGN KEY (gid)
   REFERENCES games(gid)
   ON DELETE CASCADE;

推荐答案

我很确定您不能简单地将 on delete cascade 添加到现有的外键约束.您必须先删除约束，然后添加正确的版本.在标准 SQL 中，我认为最简单的方法是

I'm pretty sure you can't simply add on delete cascade to 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

• 开始交易，
• 删除外键，
• 用删除级联添加外键，最后
• 提交交易

对要更改的每个外键重复.

Repeat for each foreign key you want to change.

但是 PostgreSQL 有一个非标准扩展，它允许您在单个 SQL 语句中使用多个约束子句.例如

But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. For example

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")，或者您可以查询信息架构.

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.

select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null

这篇关于如何添加“删除级联"约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！