How to Remove Constraints from a SQL Server Table

Problem

I need to drop constraints on a number of my SQL Server tables. Most of our constraints are primary key and foreign keys, but there are other constraints too. How can we script it so it will deploy with a pipeline in a repeatable manner?

Solution

Here is a quick refresher on constraint types in SQL Server. We will focus on SQL Server 2022 in our examples, but constraints have been a part of SQL Server for decades.

Primary Key – Identifies the unique rows in a table and can be made up of one or more columns. This is the most frequently used constraint in SQL Server.
Foreign Key – Identifies how a table is related to another table and may be comprised of one or more columns. It is also possible to have a self-referencing foreign key that references its own primary key.
Check Constraint – Checks to ensure a value being inserted into the table meets the requirement for what is valid for that column.
Default Constraint – Creates a value on data being inserted when one is not specified for a column.
Unique Constraint – Enforces uniqueness on a column.

Finding Constraints on Tables

Microsoft provides a stored procedure, sp_helpconstraint, to show the constraints on tables.

The syntax is:

--MSSQLTips.com
sp_helpconstraint [ @objname = ] N'objname'

Running this against a table will return the constraint(s) on that table and optionally, additional information about the constraints for check and default constraints.

Alternatively, use the information_schema view TABLE_CONSTRAINTS to list constraints.

--MSSQLTips.com
select * from information_schema.table_constraints order by TABLE_NAME

SSMS also shows constraints and allows them to be dropped using the GUI:

Dropping a Constraint with SQL

To drop a constraint, use an alter table statement as follows.

--MSSQLTips.com
ALTER TABLE [Table_name] DROP CONSTRAINT Constraint_Name

Dropping Primary Keys and Foreign Keys

When dropping the primary key on a table, you must first drop foreign keys (if any) that relate to the primary key.

We will create two tables where the second table has a reference to the first table with the following:

-- MSSQLTips.com
CREATE TABLE [Customers](
   [CustomerID] [int] NOT NULL,
   [CustomerName] [nvarchar](100) NOT NULL
   CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
   ([CustomerID] ASC)
)
 
INSERT INTO [Customers]
values
 (1, 'Murphy Brothers')
,(2, 'Anderson supply corporation')
,(3, 'Foxtail brush incorporated')
,(4, 'Souvenier company of Nashville')
 
 
CREATE TABLE [Customer_Preferences](
   [CustomerID] [int] NOT NULL
   ,[PreferenceName] [nvarchar](100) NOT NULL
   ,[Value] [nvarchar](100) not null
   CONSTRAINT [PK_Customer_Prefs] PRIMARY KEY CLUSTERED 
   ([CustomerID] ASC,[PreferenceName])
   CONSTRAINT FK_CustomerID FOREIGN KEY ([CustomerID])
REFERENCES [Customers] ([CustomerID]))
 
INSERT INTO [Customer_Preferences]
values
 (1,'Loading Dock','Back')
,(1,'Attention To on package', 'Receiving')
,(1,'Contact','Debbie in receiving')
,(2,'Loading Dock', 'Bay street entrance')
,(3, 'Loading Dock','Call when near for instructions')
,(3,'Hours', '5am to 8am ONLY')

Use the SQL Server stored procedure sp_helpconstraint to examine the first table and determine that there is a foreign key that must be dropped first before the primary key can be dropped.

-- MSSQLTips.com
exec sp_helpconstraint @objname='Customers';

The results of this show that there is a foreign key alerting the DBA that it must be dropped first if we are going to drop the primary key on the first table.

Here are the commands to drop the constraints.

-- MSSQLTips.com
-- Drop the foreign key first
ALTER TABLE [Customer_Preferences] DROP CONSTRAINT FK_CustomerID;
-- We can now drop the primary key from the first table
ALTER TABLE [Customers] DROP CONSTRAINT PK_Customers;

Dropping Check Constraints and Default Constraints

There is no requirement that a check constraint or a default constraint be used on the same table or columns.

In our example below, we will work through a table with both types of constraints on a single column.

--MSSQLTips.com
CREATE TABLE [Orders]
([OrderID] [int] NOT NULL
,[Item] nvarchar(100) NOT NULL
,[DateOrdered] datetime2 CONSTRAINT df_date_ordered DEFAULT getdate() 
,CONSTRAINT chk_date
   CHECK (DateOrdered > dateadd(day,-365,getdate()))
)
 
INSERT INTO [Orders]
([OrderID],[ITEM])
VALUES
 (10110,'Apple')
,(10111,'Orange')
,(10112,'Pear')
 
INSERT INTO [Orders]
([OrderID],[ITEM],[DateOrdered])
VALUES
 (10001,'Avocado','1-12-2025 2:35:00')
,(10050,'Beans','1-22-2025 15:05:00')
,(10100,'Rice','1-28-2025 18:01:00')

When we run the command to retrieve the constraint information, exec sp_helpconstraint @objname='Orders'; we see additional information we didn’t see with primary and foreign keys. We also can see the expression defining the constraints on the columns.

Dropping the constraints is accomplished with the same ALTER TABLE command. Since the constraints are independent of each other, you can drop one or both as needed.

--MSSQLTips.com
ALTER TABLE [Orders] DROP CONSTRAINT df_date_ordered; 
ALTER TABLE [Orders] DROP CONSTRAINT chk_date;

Dropping Unique Constraints

While unique constraints work very similarly to a Primary Key, they are not bound in any way to a Primary Key.

We will create a table with a Primary Key and identical unique constraint that reference the same columns to demonstrate.

--MSSQLTips.com
 
CREATE TABLE [Player_Stats]
(id int identity
,[Username] nvarchar(100)
,[Ranking] int
,[Region] nvarchar(100)
,[Wins] int
,[Losses] int
,CONSTRAINT [PK_id] PRIMARY KEY CLUSTERED(id, Username, Region)
,CONSTRAINT UQ_Username_Region UNIQUE(ID, Username, Region)
)
 
INSERT INTO [Player_Stats] 
VALUES
 ('GreatHero01', 1157, 'North America', 3671,5701)
,('PlasticWrapper', 897, 'Euro', 157, 261)
,('GreatHero01', 271, 'Euro', 85,63)
,('GmrGrl01',1499,'North America', 7855, 8155)

Now let’s try to drop the primary key constraint.

--Drop the primary key
ALTER TABLE [Player_Stats] DROP CONSTRAINT PK_ID;

We see the Primary Key is dropped successfully even with a unique constraint on the same columns.

Primary key is dropped while having unique constraint

Dropping the unique constraint yields the same result illustrating the two constraints are not bound to each other.

--MSSQLTips.com
ALTER TABLE [Player_Stats] DROP CONSTRAINT UQ_Username_Region;

Dropping the constraint does not affect key

Next Steps

Work through the examples of Primary Key and Foreign Keys to understand how they relate to each other and affect what is needed to drop each type of constraint.
Review the Microsoft article on TABLE_CONSTRAINTS to understand how you can be prepared when you need to work with a large number of tables.
Test the process in Dev \ Test \ QA environment.
Check out these resources:
- sp_helpconstraint documentation from Microsoft.
- Documentation on the TABLE_CONSTRAINTS information schema view.
- Creating primary and foreign keys: Script Constraints, Primary and Foreign Keys for SQL Server Database
- Additional articles on constraints: What is a SQL Constraint – Null, Check, Default, Unique, Primary Key

Resources

Community

Subscribe