Removing (dropping) a Foreign Key Constraint in PostgreSQL

Had a need to drop a Foreign Key Constraint in PostgreSQL 8.x today, and this is how you do it:

database=> \d table_name;
Table "public.table_name"
Column | Type | Modifiers
id | integer |
field | character varying(20) |
field_description | character varying(150) |
Foreign-key constraints:
"table_name_id_fkey" FOREIGN KEY (id) REFERENCES other_table(id) ON DELETE CASCADE

database=> ALTER TABLE table_name DROP CONSTRAINT "table_name_id_fkey";

As simple as that. The name of the constraint is shown when describing the table with \d under “Foreign-key constraints”, and you simply do an ALTER statement to drop the constraint.

3 thoughts on “Removing (dropping) a Foreign Key Constraint in PostgreSQL”

  1. Thanks Mats! I searched for this on Google and your page popped up. I didn’t see your name at first, but it somehow appeared on my retina after I had left the page so I had to go back and check.

    Hope you are doing good!

    And, yes, the constraint is gone.

    Lars, currently in Palo Alto.

  2. ya it works very well really…thank…plz help me that how we rollback on using update command cause i have a serious problem in my company…….plz help me regarding this mail me i m using PostgreSQL okays…….

  3. Still useful 9 years later :)
    I added “CASCADE” to the ALTER TABLE command as the regular command was locking up my psql session:

    ALTER TABLE activities
    DROP CONSTRAINT “activities_created_by_fkey” CASCADE;

Leave a Reply

Your email address will not be published. Required fields are marked *