adviseranna.blogg.se

• #Alter table add foreign key postgres update

You will need to repeat the above queries for each dependent table.Īfter that, all dependent tables will point to your new another_id column. And finally we're creating new foreign key constraint pointing to the another_id instead of idĪLTER TABLE foo_table ADD CONSTRAINT fk_e52ffdeea76ed395 FOREIGN KEY (user_id) REFERENCES users (another_id) ON DELETE CASCADE

#Alter table add foreign key postgres update

UPDATE foo_table T SET user_id = (SELECT another_id FROM users WHERE id = T.user_id) Then, we're swapping values in foreign key column from id to another_id Here's how you can do it: - We are dropping the foreign key constraint on dependant table (in other case it will prevent us from updating the values)ĪLTER TABLE foo_table DROP CONSTRAINT fk_e52ffdeea76ed395 You will need to replace the IDs stored in those columns from id to another_id. Solutionįirst of all, you have three tables ( foo_table, bar_table, baz_table) which are pointing to your users table by means of foreign keys (called user_id in all cases). I will publish it here for future reference. I've spent some time and finally came up with a working solution. How do I replace primary key in PostgreSQL table from id column to another_id column and maintain data integrity? "fk_e52ffdeea76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE Here's foo_table description: Table "public.foo_table" TABLE "baz_table" CONSTRAINT "fk_83adbaf0a76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

TABLE "bar_table" CONSTRAINT "fk_72936b1da76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE TABLE "foo_table" CONSTRAINT "fk_4affc6e5a76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE Here's the users table description: Table "ers"Ĭolumn | Type | Modifiers | Storage | Stats target | Description

There are other tables that reference users by primary key. The id is a primary key, the another_id is just another integer column with unique constraint. I have users table in my PostgreSQL 9.3.6 database with two columns: id and another_id.