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

问题描述

在PostgreSQL 8中，可以在下表中的两个外键上添加删除级联，而不删除后者？

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 pref_scores
        Table "public.pref_scores"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 id      | character varying(32) |
 gid     | integer               |
 money   | integer               | not null
 quit    | boolean               |
 last_ip | inet                  |
Foreign-key constraints:
   "pref_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES pref_games(gid)
   "pref_scores_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

这两个引用表如下：

# \d pref_games
                                     Table "public.pref_games"
  Column  |            Type             |                        Modifiers
----------+-----------------------------+----------------------------------------------------------
 gid      | integer                     | not null default nextval('pref_games_gid_seq'::regclass)
 rounds   | integer                     | not null
 finished | timestamp without time zone | default now()
Indexes:
    "pref_games_pkey" PRIMARY KEY, btree (gid)
Referenced by:
    TABLE "pref_scores" CONSTRAINT "pref_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES pref_games(gid)


# \d pref_users
                Table "public.pref_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:
    "pref_users_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "pref_cards" CONSTRAINT "pref_cards_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_catch" CONSTRAINT "pref_catch_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_chat" CONSTRAINT "pref_chat_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_game" CONSTRAINT "pref_game_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_hand" CONSTRAINT "pref_hand_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_match" CONSTRAINT "pref_match_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_payment" CONSTRAINT "pref_payment_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_rep" CONSTRAINT "pref_rep_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id)
    TABLE "pref_rep" CONSTRAINT "pref_rep_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_scores" CONSTRAINT "pref_scores_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
    TABLE "pref_status" CONSTRAINT "pref_status_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

还有我想知道添加2个索引是否有意义到前表？

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

更新：谢谢，还有我在邮件列表的建议，我可以管理它in 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 no need a transaction:

ALTER TABLE public.pref_scores
DROP CONSTRAINT pref_scores_gid_fkey,
ADD CONSTRAINT pref_scores_gid_fkey
   FOREIGN KEY (gid)
   REFERENCES pref_games(gid)
   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

• 开始一个事务，


• 删除外键


• 在删除级联上添加的外键，最后


• 提交交易

• start a transaction,
• drop the foreign key,
• add a foreign key with on delete cascade, and finally
• commit the transaction

对每个要更改的外键重复上述操作。

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.pref_scores
drop constraint pref_scores_gid_fkey,
add constraint pref_scores_gid_fkey
   foreign key (gid)
   references pref_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屋！