Postgres database – remove foreign key from a table

April 23rd, 2008

To remove a foreign key constraint from a Postgres database, you must copy the table,  delete it,  and then re-create it!

e.g. if you have a table called ‘log’ with a foreign key ‘fk’ – I tried using:

ALTER TABLE log DROP CONSTRAINT fk;

This does work for check constraints but not for foreign key constraints.

It took a while to find this out and reiterated the fact that I don’t like Postgres!

In MySQL you can use:

ALTER TABLE log DROP FOREIGN KEY fk;

So this is what you’d do for Postgres:

CREATE TABLE temp AS SELECT * FROM log;
DROP TABLE log;
CREATE TABLE log AS SELECT * FROM temp;
DROP TABLE temp;

Link from postgressql.com

Posted by bobak Filed in programming
1 Comment »

One Response to “Postgres database – remove foreign key from a table”

  1. C Says:
    October 6th, 2008 at 8:22 am

    No you don’t, you just have to get the syntax right, try:

    ALTER TABLE log DROP CONSTRAINT fk_fkey;

    …assuming the foreign key was created like:

    ALTER TABLE log ADD FOREIGN KEY fk REFERENCES …

    or you could have specifically named the foreign key with:

    ALTER TABLE log ADD CONSTRAINT myfk FOREIGN KEY fk REFERENCES …

    the delete with…

    ALTER TABLE log DROP CONSTRAINT myfk;

    [Of course if you’re using Postgres < 7.3 then deleting foreign keys is more painful, but the comparable versions of MySQL (<4.0) had no foreign key support. MySQL 5.0 should be compared with no less than Postgres 8 in the interests of fairness to both parties.]

Leave a Reply