postgresql – 如何添加“关于删除级联”约束？

发布时间：2020-12-13 16:53:54 所属栏目：百科来源：网络整理

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

# 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 index’es到前表？

更新：谢谢，我也有在邮件列表的建议，我可以管理它在1语句，因此不需要一个事务：

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中，我相信最简单的方法是做到这一点

>开始一个事务，
>丢弃外键，
>添加外键与删除级联，最后
>提交事务

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

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

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，或者展开层次结构直到你看到“约束”)，或者你可以query the information schema。

select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!