1

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

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Irenej Marc
  • 287
  • 3
  • 9

2 Answers2

1

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:

A foreign key must reference columns that either are a primary key or form a unique constraint.

Bold emphasis mine.

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 Fiddle. (With queries to show catalog entries providing names amongst other things.)

Details in the manual on ALTER TABLE.

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • If I understand this correctly, CREATE UNIQUE index indexname ON foo(id); should work. I tried that and it still doesn't let me drop the primary key. – Irenej Marc Jan 08 '15 at 02:56
  • @Seleren: No, you need a UNIQUE ***constraint***, which is subtly different. Adding code example. – Erwin Brandstetter Jan 08 '15 at 03:04
  • Sounds like the correct answer, but as I have at least 10 foreign keys referencing the table I want to create the composite key in, this doesn't work very well for me. Still, thank you for taking your time. – Irenej Marc Jan 08 '15 at 09:45
  • @Seleren: You could generate a script from the catalog files. Basic example for an FK constraint with default features here: http://stackoverflow.com/questions/12395466/postgresql-drop-constraint-with-unknown-name/12396684#12396684. – Erwin Brandstetter Jan 09 '15 at 03:35
1

I presume that you will want to change all the referencing foreign keys to use the new primary key, right? So you are going to have to drop all these foreign keys, then drop the primary key, then create the new primary key, then create new foreign keys that reference the new primary key. So if there are 10 FKs that reference this table, you'll have to do 10 drops of the FKs, 1 drop of the old PK, 1 alter to add the new PK, then 10 alters to add the new FKs.

I don't have an instance of Postgres handy, but I don't think there's any shortcut to this. I think you have to do all 22 steps. But you don't have to drop the database, just all the FKs.

If you want the old FKs to continue to point to the old PK, which will no longer be the PK when you're done, then as Erwin says, you should be able to just add a unique constraint on it. Though I think his example is backward in that you'd have to add the unique constraint BEFORE dropping the PK or you'd get the same message. As I say, don't have a copy of Postgres handy to check this. In Sql Server, you can add a unique index, then drop the pk, then create the new pk. I suppose worst case would be that you'd have to drop the 10 FKs and then re-create them.

Jay
  • 26,876
  • 10
  • 61
  • 112
  • True, but since I'm using a DB designer I just dropped the whole database and recreated it, less work than dropping all the FKs. – Irenej Marc Jan 09 '15 at 16:08