I know there are a lot of references out there for what I am going to mention.
I am rather sharing something I feel should be very useful and consolidating in one place – Q&A-style. I have struggled earlier finding this for different constraints on different time.
The following constraints are commonly used:
- Primary key Constraint
- Foreign key Constraint
- Unique Constraint
- Not Null Constraint
- Default Constraint
- Check Constraint
It is pretty easy to DROP a constraint if you know the name –
ALTER TABLE {table_name} DROP CONSTRAINT {constraint_name};
But most of the times we use to define constraints at the time of creating tables and without name. Some time later, if we decide to drop any constraint, it is not that straightforward and we do need to write some comparatively complex queries.
So, how do we do this?
Advertisement
Answer
-- Table T1
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE T1
CREATE TABLE T1 (COL1 INT PRIMARY KEY)
-- Table T2
IF OBJECT_ID('dbo.T2') IS NOT NULL
DROP TABLE T2
CREATE TABLE T2 (
COL1 INT FOREIGN KEY REFERENCES T1(COL1)
,COL2 VARCHAR(2) UNIQUE
,COL3 INT NOT NULL DEFAULT(0)
,CHECK (
COL3 IN (
0
,1
,2
)
)
)
GO
-- *** Foreign key constraint ***
DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL1'
DECLARE @Command NVARCHAR(1000)
SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @Table
AND COLUMN_NAME = @Column
--PRINT (@Command) -- ALTER TABLE [T2] DROP CONSTRAINT FK__T2__COL1__058EC7FB
EXECUTE (@Command)
GO
-- *** Primary key constraint ***
DECLARE @Table NVARCHAR(256) = N'T1'
DECLARE @Command NVARCHAR(1000)
SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + [name]
FROM sys.key_constraints
WHERE [type] = 'PK'
AND [parent_object_id] = OBJECT_ID(@table);
--PRINT (@Command) -- ALTER TABLE [T1] DROP CONSTRAINT PK__T1__AA1D004EBAE57D94
EXECUTE (@Command)
GO
-- *** Unique key constraint ***
DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL2'
DECLARE @Command NVARCHAR(1000)
SELECT @Command = 'ALTER TABLE [' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.indexes d ON d.object_id = t.object_id
AND d.type = 2
AND d.is_unique = 1
JOIN sys.index_columns ic ON d.index_id = ic.index_id
AND ic.object_id = t.object_id
JOIN sys.columns c ON ic.column_id = c.column_id
AND c.object_id = t.object_id
WHERE t.name = @Table
AND c.name = @Column
--PRINT (@Command) -- ALTER TABLE [T2] DROP CONSTRAINT UQ__T2__AA1D0040A435D4E0
EXECUTE (@Command)
GO
-- *** Not Null constraint ***
-- ALTER TABLE T2 ALTER COLUMN COL3 INT NULL
-- GO
DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL3'
DECLARE @Command NVARCHAR(1000)
SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.default_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
AND c.column_id = d.parent_column_id
WHERE t.name = @Table
AND c.name = @Column
--PRINT (@Command) -- ALTER TABLE [T2] DROP CONSTRAINT DF__T2__COL3__0682EC34
EXECUTE (@Command)
GO
-- *** Check constraint ***
DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL3'
DECLARE @Command NVARCHAR(1000)
SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.check_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
AND c.column_id = d.parent_column_id
WHERE t.name = @Table
AND c.name = @Column
--PRINT (@Command) -- ALTER TABLE [T2] DROP CONSTRAINT CK__T2__COL3__0777106D
EXECUTE (@Command)
Some of the queries are from my local repository and some are taken references from different sources:
Finding a Primary Key Constraint on the fly in SQL Server 2005
Drop Foreign Key without knowing the name of the constraint?