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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

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

Answer 6

Here goes my own version that drops all dependent constraints -- default constraint (if exists) and all affected check constraints (as SQL standard seems to suggest and as some other databases seem to so)

declare @constraints varchar(4000);
declare @sql varchar(4000);
with table_id_column_position as (
   select object_id table_id, column_id column_position
      from sys.columns where object_id is not null and object_id = object_id('TableName') and name = 'ColumnToBeDropped'
)
select @constraints = coalesce(@constraints, 'constraint ') + '[' + name + '], '
from sysobjects 
where (
  -- is CHECK constraint
  type = 'C' 
  -- dependeds on the column
  and id is not null
  and id in (
      select object_id --, object_name(object_id)
      from sys.sql_dependencies, table_id_column_position 
      where object_id is not null
      and referenced_major_id = table_id_column_position.table_id
      and referenced_minor_id = table_id_column_position.column_position
    )
) OR (
  -- is DEFAULT constraint
  type = 'D'
  and id is not null
  and id in (
    select object_id
    from sys.default_constraints, table_id_column_position
     where object_id is not null
     and parent_object_id = table_id_column_position.table_id
     and parent_column_id = table_id_column_position.column_position
  )
);
set @sql = 'alter table TableName drop ' + coalesce(@constraints, '') + ' column ColumnToBeDropped';
exec @sql

(Beware: both TableName and ColumnToBeDropped appear twice in the code above)

This works by constructing single ALTER TABLE TableName DROP CONSTRAINT c1, ..., COLUMN ColumnToBeDropped and executing it.