在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
