Update column of a table with new foreign key of associated table

Let’s say I have a Persons and Books table that were associated.

PERSONS TABLE                               BOOKS TABLE

 uid | userCode | name                   id|   name   | owner
-------------------------                --------------------------
  1  |  0xc!    | john                   1 | book foo | 0xc!
  2  |  li5$    | doe                    2 | foo book | li5$
  3  |  1y&t    | temp                   3 |   ddia   | 0xc!

Currently persons.usercode serves as the primary key and hence the foreign key on associated tables. I would like to change the primary key of the persons table to persons.uid. So now I want the books table to look like

PERSONS TABLE                               BOOKS TABLE

 uid | usercode | name                   id|   name   | owner
-------------------------                --------------------------
  1  |  0xc!    | john                   1 | book foo | 1
  2  |  li5$    | doe                    2 | foo book | 2
  3  |  1y&t    | temp                   3 |   ddia   | 1

Dropping and adding the new primary key constraint shouldn’t be a problem. However, how do I go about updating the entire books.owner column with the new primary key if I have over 10,000 rows in the books table

Answer

You need to drop/disable the current foreign key & re-add it. You may also need to find out the name of that primary/foreign key constraint before dropping.

ALTER TABLE "PERSONS" 
DROP CONSTRAINT "primary_fkey"

UPDATE BOOKS bk SET owner=(SELECT uid FROM PERSONS WHERE userCode = bk.owner);

ALTER TABLE "PERSONS" 
ADD CONSTRAINT "primary_fkey" 
FOREIGN KEY ("uid")
REFERENCES BOOKS("owner")
ON UPDATE CASCADE;