Using the SQL Server 2016 syntax how to use drop constraint 'if exists' when the table itself may or may not exist?

Question

I've researched how to test for the existence of a table or constraint when deleting database items in SQL Server 2016 and learned that the 'if exist' syntax can be used for this. But I haven't worked out how to delete a table's constraints, followed by deleting the table itself, in cases where the table itself may or may not exist without the script erroring.

-- Drop TABLE1
alter table TABLE1 drop constraint if exists F_TABLE1_COLUMN1
go
alter table TABLE1 drop constraint if exists F_TABLE1_COLUMN2
go
alter table TABLE1 drop constraint if exists P_TABLE1_COLUMN2
go
drop table if exists TABLE1
go

In this example the script will error attempting to delete the constraint if the table does not exist.

How should I script this?

I think here you should use the old way to check if the table exists or not. — Alexey Usharovski, Dec 10 '19 at 10:24
A constraint is part of the table. It doesn't exist by itself — Panagiotis Kanavos, Dec 10 '19 at 10:24
What, precisely, is the point of first dropping the constraints separately if you're going to be dropping the table anyway? In particular, dropping a primary key constraint is not a free operation if it's also the clustered index (which is quite common); the table will be rewritten as a heap, causing pointless data churn since right after it will cease to exist. — Jeroen Mostert, Dec 10 '19 at 10:26
the problem of dropping a table isn't with constraints in that table, but normally with constraints in other tables that are referencing that table. — LukStorms, Dec 10 '19 at 10:31
I could understand you doing this if the constraints were on a different table, but i completely agree with @JeroenMostert here; dropping constraints on a table you're then going to drop is completely pointless. It would be like individually deleting every file in a directory, and then deleting the directory (just delete the directory, and the files go with it). — Thom A, Dec 10 '19 at 10:32
Regarding it being useless to drop the constraints separately would that also be true of indexes? — Neutrino, Dec 12 '19 at 10:38
Yes, all indexes will also be dropped when a table is dropped. There is no added value in first dropping all indexes separately. And just as with the primary key, if you drop the clustered index this way you will in fact incur unnecessary overhead as the table is rewritten to a heap. The only things for which it makes sense to drop them first are those that refer to the table but aren't part of it -- foreign keys on other tables, functions, procedures, views. — Jeroen Mostert, Dec 13 '19 at 13:41

score 1 · Answer 1 · answered Dec 10 '19 at 10:40

1

You can check to see if a table exists as stated here

add your code for dropping constraint within the block as :

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = <table_name>))
BEGIN
    ALTER TABLE <table_name>   
    DROP CONSTRAINT <Constraint_name>;
END

answered Dec 10 '19 at 10:40

dataconsumer

196
1
5

suggesting you to use system catalog views instead of INFORMATION_SCHEMA views, as INFORMATION_SCHEMA views are not being updated regularly for DB improvements. – Venkataraman R Dec 10 '19 at 12:28

Gordon Linoff · Answer 2 · 2019-12-10T12:43:35.390

0

First, you don't need to delete the constraints separately. They will be deleted with the table.

You can delete each constraint independently using try/catch blocks.

So the equivalent of your if exists is:

begin try
    alter table TABLE1 drop constraint F_TABLE1_COLUMN1;
end try
begin catch
end catch;

You can also delete the table an all associated constraints using dynamic SQL:

exec sp_executesql 'drop table TABLE1';

edited Dec 10 '19 at 12:43

answered Dec 10 '19 at 12:25

Gordon Linoff

1,242,037
58
646
786

1

This will not work if `TABLE1` actually does not exist (per the premise of the question), as the statement will fail to compile. – Jeroen Mostert Dec 10 '19 at 12:27
@JeroenMostert . . . Good catch. I was distracted by the desire to delete the constraints before deleting the table. – Gordon Linoff Dec 10 '19 at 12:44

Venkataraman R · Accepted Answer · 2019-12-10T13:02:47.450

0

From SQL Server 2016 onwards, you have DROP IF EXISTS syntax. First check the existance of the table, before going ahead with dropping of constraint, Table.

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('<TableName>'))
BEGIN
    ALTER TABLE <TableName> DROP CONSTRAINT IF EXISTS <CONSTRAINTNAME>;
    DROP TABLE IF EXISTS <TableName>;
END
GO

Also, if you are planning to drop the table, you dont need to drop the constraint. You can simply drop the table directly. Below would suffice.

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('<TableName>'))
BEGIN
    DROP TABLE IF EXISTS <TableName>;
END
GO

But, I would suggest you to check and drop the foreign keys associated with this table and then drop this table. Otherwise you will get error. You can check the foreign keys associated with the table from the below script. First you need to drop them, before dropping the tables.

EXEC sp_fkeys [ @pktable_name = ] 'pktable_name'

edited Dec 10 '19 at 13:02

answered Dec 10 '19 at 12:33

Venkataraman R

12,181
2
31
58

No. OP has mentioned column name in the place of constraint name – Venkataraman R Dec 10 '19 at 12:35
That seems to be pure speculation on your end, as there's no actual table definition included. I'd consider it very unlikely that `F_TABLE1_COLUMN2` and `P_TABLE1_COLUMN2` are column names, but if you actually thought that was the problem you should clarify that with a comment. – Jeroen Mostert Dec 10 '19 at 12:38
Sorry. I agree with you. I misunderstood. I will take care in future. I have updated my answer with additional information to add value. – Venkataraman R Dec 10 '19 at 13:03

Using the SQL Server 2016 syntax how to use drop constraint 'if exists' when the table itself may or may not exist?

3 Answers3