Drop a column with a default constraint in SQL Server (IF EXISTS)
sql, sql-server-2008
Solution
In SQL Server 2005 upwards you can drop both the constraint and the column in one statement.
The syntax is
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ]
The emphasis is on [ ,...n ], indicating multiple terms.
NB! Since the terms are processed sequentially, if the column being dropped is part of the constraint being dropped, then the constraint must be the first term, followed by the column term.
In your example:
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]
So your code would be:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]
END
GO
In SQL Server 2016 they have introduced the IF EXISTS clause which removes the need to check for the existence of the constraint first e.g.
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT IF EXISTS [DF_Employees_EmpID], COLUMN IF EXISTS [EmpID]
Problem
I am writing a sql script for dropping column and a default constraint. The following script works fine but i like to know if it is a right way of doing it. Can i drop a default constraint with a column in one statement instead of using two separate ones? ``` IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID] END GO BEGIN ALTER TABLE [dbo].[Employees] DROP COLUMN [EmpID] END ```