Created
April 25, 2017 22:03
-
-
Save vladimirmyshkovski/d6b41cffe80f38adb43852a12b9a4af1 to your computer and use it in GitHub Desktop.
How to change FOREIGN KEY of an existing PostgreSQL table?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- We are dropping the foreign key constraint on dependant table (in other case it will prevent us from updating the values) | |
ALTER TABLE foo_table DROP CONSTRAINT fk_e52ffdeea76ed395; | |
-- Then, we're swapping values in foreign key column from id to another_id | |
UPDATE foo_table T SET user_id = (SELECT another_id FROM users WHERE id = T.user_id); | |
-- And finally we're creating new foreign key constraint pointing to the another_id instead of id | |
ALTER TABLE foo_table ADD CONSTRAINT fk_e52ffdeea76ed395 FOREIGN KEY (user_id) REFERENCES users (another_id) ON DELETE CASCADE; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 1. Dropping the original primary key | |
ALTER TABLE users DROP CONSTRAINT users_pkey | |
-- 2. Renaming existing index for another_id (optional) | |
ALTER INDEX uniq_1483a5e93414710b RENAME TO users_pkey | |
-- 3. Creating new primary key using existing index for another_id | |
ALTER TABLE users ADD PRIMARY KEY USING INDEX users_pkey | |
-- 4. Creating index for old id column (optional) | |
CREATE UNIQUE INDEX users_id ON users (id) | |
-- 5. You can drop the original sequence generator if you won't need it | |
DROP SEQUENCE users_id_seq |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment