Updating primary keys in POSTGRESQL

Question

I have a database from previous project that I want to use in another project, from security reasons I need to update the IDs of one of the table. Problem is that the table is heavily referenced by foreign keys from other tables:

CREATE TABLE "table_table" (
     "id" serial NOT NULL PRIMARY KEY,
     "created" timestamp with time zone NOT NULL,
);


CREATE TABLE "table_photo" (
     "id" serial NOT NULL PRIMARY KEY,
     "table_id" integer NOT NULL REFERENCES "table_table" ("id") DEFERRABLE INITIALLY DEFERRED,
);

Now if I change the id on table_table the reference from table_photo won't work. I will probably use something like this to change the IDs:

UPDATE table_table SET id = id + 15613;

I have read somewhere that I could use ON UPDATE CASCADE constraints to do this but I am not very sure how to use it.

btw: I am using Django ORM.

Solution

Get the constraint name with \d "table_photo", which shows:

Foreign-key constraints:
    "table_photo_table_id_fkey" FOREIGN KEY (table_id) REFERENCES table_table(id) DEFERRABLE INITIALLY DEFERRED

Then replace it with a constraint that has on update cascade:

ALTER TABLE "table_photo"
  DROP CONSTRAINT "table_photo_table_id_fkey",
  ADD CONSTRAINT "table_photo_table_id_fkey" 
    FOREIGN KEY ("table_id")
    REFERENCES "table_table"
    ON UPDATE CASCADE
    DEFERRABLE INITIALLY DEFERRED;

Now when you do your UPDATE, referenced row IDs are automatically updated. Adding an index on "table_photo"."table_id" will help a lot.

---

This can be slow for big tables though. An alternative if you have large tables is to do it in a couple of stages. For table A with field id that's referenced by table B's field A_id:

• Add a new column, new_id, to A, with a UNIQUE constraint. Leave it nullable.
• Add a new column, A_new_id to table B, giving it a foreign key constraint to A(new_id).
• Populate A.new_id with the new values

• Do an

  UPDATE B
  SET A_new_id = A.new_id 
  FROM A
  WHERE B.A_id = A.id; 
  

  to do a joined update, setting the new ID values in B.A_new_id to match.

• Drop the column B.A_id and rename B.A_new_id to B.A_id.
• Drop the column A.id and rename A.new_id to A.id
• Create a PRIMARY KEY constraint on the renamed A.id, USING the index created automatically before.

It's a lot more complicated, especially since for big tables you usually want to do each of these steps in batches.

If this seems too complicated, just do it with a cascading foreign key constraint like above.

Comments

[Craig Ringer]

Good on you for including table definitions. Thanks.

[Visgean Skeloru]

I had to separate ADD and DROP constraints to its own alter statements but it worked.

[Simon D]

Needs a comma between the drop constraint and add constraint otherwise you'll get a syntax error - I'll edit it in

[Prem]

After update, do we need to do any reset for changed constraint ON UPDATE CASCADE?