将主键更改为复合主键 [英] Changing a primary key to a composite primary key

问题描述

我一直在为我的一张表使用普通"(非复合)主键.现在，我想将其更改为复合主键.我的桌子看起来像这样:

I've been using a "normal" (non-composite) primary key for one of my tables. Now I want to change it to a composite primary key. My tables look something like this:

-- Table 1
CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    id2 INT,
    ...
)

-- Table 2
CREATE TABLE bar (
    id SERIAL PRIMARY KEY,
    id_foo INT REFERENCES foo (id)
)

这里的问题是psql不想删除旧的主键，因为其他表引用了它.

The problem here is that psql does not want to drop the old primary key, since other tables reference it.

有什么办法可以解决这个问题而又不会删除整个数据库?

Is there any way of getting around this without dropping the whole database?

推荐答案

您可以添加冗余的每个文档:

You can add a redundant UNIQUE constraint on id before you drop the PRIMARY KEY constraint. That satisfies the requirement of FK constraints. Per documentation:

外键必须引用作为主键或形成唯一约束的列.

强调粗体.

很明显，FK约束与它在 pg_depend .因此，您需要删除并稍后重新创建所有引用的FK约束或将系统表弄乱(不建议这样做！).最好的一项交易，以保持参照完整性:

Obviously, the FK constraint is bound to the PK constraint it was created with explicitly in pg_depend. So you need to drop and later recreate all referencing FK constraints or mess with system tables (which is not advisable!). Best in one transaction to keep referential integrity intact:

BEGIN;
ALTER TABLE bar DROP CONSTRAINT bar_id_foo_fkey;

ALTER TABLE foo 
    DROP CONSTRAINT foo_pkey
  , ADD CONSTRAINT foo_uni_id UNIQUE (id)
  , ADD PRIMARY KEY (id, id2);

ALTER TABLE bar ADD CONSTRAINT bar_id_foo_fkey
  FOREIGN KEY (id) REFERENCES foo (id);

COMMIT;

SQL小提琴. (带有查询以显示目录条目中提供名称等.)

SQL Fiddle. (With queries to show catalog entries providing names amongst other things.)

手册上的详细信息.

Details in the manual on ALTER TABLE.

这仅在中间状态时才有意义.如果 id 保持 UNIQUE NOT NULL ，则它也可能是PK.

This only makes sense as intermediate state. If id stays UNIQUE NOT NULL, it might as well be the PK.

这篇关于将主键更改为复合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！