DROP Constraint without knowing the name

Question

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?

Answer 1

-- 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?

How to drop a unique constraint from table column?