{"id":33,"date":"2008-04-23T13:10:12","date_gmt":"2008-04-23T12:10:12","guid":{"rendered":"http:\/\/www.bob-ak.com\/?p=35"},"modified":"2008-04-23T13:10:12","modified_gmt":"2008-04-23T12:10:12","slug":"postgres-database-remove-foreign-key-from-a-table","status":"publish","type":"post","link":"https:\/\/bob-ak.com\/?p=33","title":{"rendered":"Postgres database &#8211; remove foreign key from a table"},"content":{"rendered":"<p>To remove a foreign key constraint from a Postgres database, you must copy the table,\u00a0 delete it,\u00a0 and then re-create it!<\/p>\n<p>e.g. if you have a table called &#8216;log&#8217; with a foreign key &#8216;fk&#8217; &#8211; I tried using:<\/p>\n<p>ALTER TABLE log DROP CONSTRAINT fk;<\/p>\n<p>This does work for check constraints but not for foreign key constraints.<\/p>\n<p>It took a while to find this out and reiterated the fact that I don&#8217;t like Postgres!<\/p>\n<p>In MySQL you can use:<\/p>\n<p>ALTER TABLE log DROP FOREIGN KEY fk;<\/p>\n<p>So this is what you&#8217;d do for Postgres:<\/p>\n<p>CREATE TABLE temp AS SELECT * FROM log;<br \/>\nDROP TABLE log;<br \/>\nCREATE TABLE log AS SELECT * FROM temp;<br \/>\nDROP TABLE temp;<\/p>\n<p><a href=\"http:\/\/www.postgresql.org\/docs\/7.2\/static\/sql-altertable.html\">Link from postgressql.com<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>To remove a foreign key constraint from a Postgres database, you must copy the table,\u00a0 delete it,\u00a0 and then re-create it! e.g. if you have a table called &#8216;log&#8217; with a foreign key &#8216;fk&#8217; &#8211; I tried using: ALTER TABLE log DROP CONSTRAINT fk; This does work for check constraints but not for foreign key [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[18,21],"class_list":["post-33","post","type-post","status-publish","format-standard","hentry","category-programming","tag-database","tag-postgres"],"_links":{"self":[{"href":"https:\/\/bob-ak.com\/index.php?rest_route=\/wp\/v2\/posts\/33","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bob-ak.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bob-ak.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bob-ak.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bob-ak.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=33"}],"version-history":[{"count":0,"href":"https:\/\/bob-ak.com\/index.php?rest_route=\/wp\/v2\/posts\/33\/revisions"}],"wp:attachment":[{"href":"https:\/\/bob-ak.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=33"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bob-ak.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=33"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bob-ak.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=33"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}