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

Question

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.)

Solution 1

If you want to do this manually, you can use Management Studio to find it (under the Constraints node inside the table).

To do it using SQL:

• If the constraints are default constraints, you can use sys.default_constraints to find it:

  SELECT OBJECT_NAME(parent_object_id) AS TableName, name AS ConstraintName
  FROM sys.default_constraints ORDER BY TableName, ConstraintName
  

• If you are looking for other constraints as well (check, unique, foreign key, default, primary key), you can use sysconstraints:

  SELECT OBJECT_NAME(id) AS TableName, OBJECT_NAME(constid) AS ConstraintName
  FROM sysconstraints ORDER BY TableName, ConstraintName
  

You do not say which version of SQL Server you are using. The above work on both SQL 2005 and SQL 2008.

Solution 2

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.

Solution 3

Or you can find it using sys.check_constraints catalog view.

Solution 4

You can find the name of the constraint out by sp_help [table name] and then drop it by name.

Or you can probably do this via Management studio.

Solution 5

For a single table and column in a single line use the following

declare @sql nvarchar(max); set @sql = ''; SELECT @sql+='ALTER TABLE [dbo].[YOURTABLENAME] DROP CONSTRAINT ' + ((SELECT OBJECT_NAME(constid) FROM sysconstraints WHERE OBJECT_NAME(id) = 'YOURTABLENAME'AND colid IN (SELECT ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS Where Table_Name = 'YOURTABLENAME' and COLUMN_NAME = 'YOURCOLUMNNAM'))) + ';'; EXEC sp_executesql @sql;

If you have multiple constraints on the column you will need to discriminate on the constraint you are after, but if you just have a default constraint this will do the trick.

Check out the other columns available in the information_schema to allow you to discriminate further.

View more solutions

Comments

[Torrents]

The first query worked great in SQL 2008 r2 when I found the table's objectID first with DECLARE @ParentObjectId INT = (select OBJECT_ID from sys.objects where name = 'ParentTable' AND type = 'U') and added WHERE parent_object_id = @ParentObjectId to the query. Otherwise you're getting all of the default constraints in the database.

[Chris]

For lazy people, (and Microsoft's SQL Server developers), in homage to this answer, replace the top two lines with CREATE PROCEDURE dbo.DropColumnDefaultOrConstraint @tableName VARCHAR(MAX), @columnName VARCHAR(MAX) AS then wrap in BEGIN and END and use EXEC DropColumnDefaultOrConstraint 'tablename', 'columnname';

[Tim]

I just don't understand why MS SQL doesn't automatically drop default constraints.

[Cee McSharpface]

this automated script is essential for any roll-your-own database schema update engine. I wonder how EF migration tool does that? the underlying flaw, that SQL server generates names for anonymous defaults and requires you to know/obtain that name in order to change or drop it again, is an issue which should have been addressed in tsql syntax itself long ago. but it is still around in sql2016. @Tim yes, or at least a syntax to force the drop.

[Tim]

@dlatikay EF doesn't handle default constraints. It won't create them by default (since there is no way to set them with attributes or fluent api), but if you manually add them EF will never check to remove them on other changes. I don't know if this is true with Automatic migrations though.

[Cee McSharpface]

@Tim the current version of EF can do it. I looked it up out of curiosity, and their approach is very similar: they retrieve the name from sys.default_constraints; look in line 577 and following, here

[Tim]

@dlatikay Oh thanks, good to know they fixed in the EF Core. I am still on 6. Worth the upgrade?

[AperioOculus]

This is nice, but it doesn't show the schema the tables are in. It would be nice if you added a join to sys.schemas.

[Marcell]

If you know the table name add a WHERE clause to the SELECT so you don't have to search in a possibly long list of table names. The auto generated names seem to contain truncated parts of the table and column name, which will let you identify the correct constraint. Good luck!