How can I ensure a foreign key constraint doesn't exist, if I don't know whether the table exists?

Question

If I know that the table exists but I don't know whether the foreign key constraint exists, then I can do this:

ALTER TABLE table_name DROP CONSTRAINT IF EXISTS constraint_name

What if I don't know whether the table itself exists? I want a single statement that has outcomes as follows:

if (the table does not exist)
{
    nothing happens
}
else if (the table exists, but the foreign key constraint does not exist)
{
    nothing happens
}
else
{
    the foreign key constraint is dropped, but the table continues to exist
}

Dale K - No, that question is asking how to find out whether a table exists. Clearly given an answer to that question, one can piece together a solution to this problem involving IF blocks, but I do not wish to do that. I want a single statement. — Hammerite, May 03 '20 at 21:42
It does say exactly that: "I want *a statement* that has outcomes as follows..." (emphasis added). "A statement" implies a single statement. — Hammerite, May 03 '20 at 21:46
If the answer is that it isn't possible, then add that as an answer. — Hammerite, May 03 '20 at 21:46

GMB · Accepted Answer · 2020-05-03T23:08:51.360

As far as your question is concerned, and as commented by Dale K, you can't do that in a single statement.

Instead, one option is to first check catalog table information_schema.referential_constraints for the existence of the constraint before attempting to drop it, like:

if (exists (
    select 1
    from information_schema.referential_constraints 
    where constraint_name  = 'myconstraint'
))
begin
    alter table mytable drop constraint myconstraint;
end

If the table does not exist, then the if condition will not be satisfied, and the alter table statement will not run.

Note that you might want to add a filter on the constraint_schema column of referential_constraints (since constraints of the same name may exist in different schemas).

How can I ensure a foreign key constraint doesn't exist, if I don't know whether the table exists?

1 Answers1