How to Drop an Orphan Foreign Key Contraint

[Ivy]

Hi,

I dropped a table without using the "cascade" option. The table was dropped but the foreign key constraint remains. When I re-created the table with the same foreign key constraint name, it returned an error stating that the contraint for relation already exists. I tried using the "alter table... drop constraint..." syntax but it did not work. Any idea I can drop the orphan foreign key constraints.

[JimKnicely]

Hi Peng,

I'm having trouble duplicating your issue.

Here is what I tried:

Code: Select all

dbadmin=> create table tab1 (c1 int, primary key (c1));
CREATE TABLE
dbadmin=> create table tab2 (c1 int, c2 int constraint tab2_fk references tab1(c1), primary key (c1));
CREATE TABLE

I can't drop the tab1 table without the CASCADE option:

Code: Select all

dbadmin=> drop table tab1;
NOTICE 4927:  The Constraint tab2_fk depends on Table tab1
ROLLBACK 3128:  DROP failed due to dependencies
DETAIL:  Cannot drop Table tab1 because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too

And if I drop the table with the FK (tab2), I can easily recreate it using the same constraint name:

Code: Select all

dbadmin=> drop table tab2;
DROP TABLE
dbadmin=> create table tab2 (c1 int, c2 int constraint tab2_fk references tab1(c1), primary key (c1));
CREATE TABLE

Maybe I don't understand the issue

[Ivy]

Sorry Jim, I am not able to reproduce the problem myself.

Earlier this moring, I tried to re-create a table with the same FK constraint name after I dropped it. Vertica did not like it and returned an error like 'the constraint already exists' and the 'create table' statement failed. After I changed the constraint name, it worked.

Well, 5 hours later, it is working for me. I will try again later. For now, please consider this case closed. Thank you for your kind assistance and sorry for the time you spent on this!