How do you drop a default value or similar constraint in T-SQL?

sql tsql constraints default
Frank Krueger picture Frank Krueger · Jul 14, 2009 · Viewed 47k times · Source

I know the syntax:

ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint]

but how to I drop the default constraint when I don't know its name? (That is, it was autogenerated at CREATE TABLE time.)

Answer

Polemarch picture Polemarch · May 25, 2012

You can use this code to do it automatically:

DECLARE @tableName VARCHAR(MAX) = '<MYTABLENAME>'
DECLARE @columnName VARCHAR(MAX) = '<MYCOLUMNAME>'
DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name 
FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
AND PARENT_COLUMN_ID = (
    SELECT column_id FROM sys.columns
    WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName))
IF @ConstraintName IS NOT NULL
    EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName)

Just replace <MYTABLENAME> and <MYCOLUMNNAME> as appropriate.

Related questions

T-SQL: How do I create a unique key that is case sensitive?

How do I create a unique constraint on a varchar field that is case sensitive (SQL Server 2005)? Currently my constraint looks like this: alter table MyTable add constraint UK_MyTable_MyUniqueKey unique nonclustered (MyCol) When I try to insert the …

Read More
Can there be constraints with the same name in a DB?

This is a follow-on question from the one I asked here. Can constraints in a DB have the same name? Say I have: CREATE TABLE Employer ( EmployerCode VARCHAR(20) PRIMARY KEY, Address VARCHAR(100) NULL ) CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More