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.
How to Drop an Orphan Foreign Key Contraint
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to Drop an Orphan Foreign Key Contraint
Hi Peng,
I'm having trouble duplicating your issue.
Here is what I tried:
I can't drop the tab1 table without the CASCADE option:
And if I drop the table with the FK (tab2), I can easily recreate it using the same constraint name:
Maybe I don't understand the issue
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
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
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: How to Drop an Orphan Foreign Key Contraint
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!
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!