CODEWITHSUNDEEP
sqlpostgresqlddl
Antonio Dragos
Antonio Dragos

Reputation: 2803

change postgres primary and foreign keys from varchar to uuid

In my database the primary and foreign keys are defined as varchars. I would like to convert them all to UUIDs (because that's what they are). I tried doing this with

ALTER TABLE customer ALTER COLUMN id TYPE uuid using id::uuid;
ALTER TABLE order ALTER COLUMN customer_id TYPE uuid using customer_id::uuid;

However if I run this script it fails after the first statement because the customer_id foreign key type does not match the primary key type. If I reverse the order of the statements it fails for the same reason.

Is there any way to migrate the primary and foreign keys to UUIDs without dropping and recreating all the key constraints?

Upvotes: 3

Views: 3542

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

You have to drop the constraints, perform the changes in the columns and then finally re-create the constraints:

ALTER TABLE orders 
  DROP CONSTRAINT orders_customer_id_fkey;

ALTER TABLE customer 
  ALTER COLUMN id TYPE uuid USING id::uuid;
ALTER TABLE orders 
  ALTER COLUMN customer_id TYPE uuid USING customer_id::uuid;

ALTER TABLE orders 
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id)
  REFERENCES customer(id);

Demo: db<>fiddle

Upvotes: 4

Related Questions