如何在不知道其名称的情况下删除 SQL 默认约束？

How to drop SQL default constraint without knowing its name?

提问by Robo

In Microsoft SQL Server, I know the query to check if a default constraint exists for a column and drop a default constraint is:

在 Microsoft SQL Server 中，我知道检查列是否存在默认约束并删除默认约束的查询是：

IF EXISTS(SELECT * FROM sysconstraints
  WHERE id=OBJECT_ID('SomeTable')
  AND COL_NAME(id,colid)='ColName'
  AND OBJECTPROPERTY(constid, 'IsDefaultCnst')=1)    
ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTable_ColName

But due to typo in previous versions of the database, the name of the constraint could be DF_SomeTable_ColNameor DF_SmoeTable_ColName.

但是由于数据库以前版本中的拼写错误，约束的名称可能是DF_SomeTable_ColName或DF_SmoeTable_ColName。

How can I delete the default constraint without any SQL errors? Default constraint names don't show up in INFORMATION_SCHEMA table, which makes things a bit trickier.

如何在没有任何 SQL 错误的情况下删除默认约束？默认约束名称不会出现在 INFORMATION_SCHEMA 表中，这让事情变得有点棘手。

So, something like 'delete the default constraint in this table/column', or 'delete DF_SmoeTable_ColName', but don't give any errors if it can't find it.

因此，类似于“删除此表/列中的默认约束”或“删除DF_SmoeTable_ColName”之类的内容，但如果找不到它，请不要给出任何错误。

回答by Philip Kelley

Expanding on Mitch Wheat's code, the following script will generate the command to drop the constraint and dynamically execute it.

扩展 Mitch Wheat 的代码，以下脚本将生成删除约束并动态执行它的命令。

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @schema_name = N'MySchema'
set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] 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_name
  and t.schema_id = schema_id(@schema_name)
  and c.name = @col_name

--print @Command

execute (@Command)

回答by Mitch Wheat

Rob Farley's blog post might be of help:

Rob Farley 的博客文章可能会有所帮助：

• Two ways to find / drop a default constraint without knowing its name

• 在不知道名称的情况下查找/删除默认约束的两种方法

Something like:

就像是：

 declare @table_name nvarchar(256)
 declare @col_name nvarchar(256)
 set @table_name = N'Department'
 set @col_name = N'ModifiedDate'

 select t.name, c.name, d.name, d.definition
 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_name
     and c.name = @col_name

回答by ScubaSteve

I found that this works and uses no joins:

我发现这有效并且不使用连接：

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName';
EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName)

Just make sure that columnName does not have brackets around it because the query is looking for an exact match and will return nothing if it is [columnName].

只需确保 columnName 周围没有括号，因为查询正在寻找精确匹配，如果它是 [columnName]，则不会返回任何内容。

回答by Ken Yao

To drop constraint for multiple columns:

删除多列的约束：

declare @table_name nvarchar(256)

declare @Command nvarchar(max) = ''

set @table_name = N'ATableName'

select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
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_name and c.name in ('column1','column2','column3')

--print @Command

execute (@Command)

回答by Jorge Garcia

Expanded solution (takes table schema into account):

扩展解决方案（考虑表架构）：

-- Drop default contstraint for SchemaName.TableName.ColumnName
DECLARE @schema_name NVARCHAR(256)
DECLARE @table_name NVARCHAR(256)
DECLARE @col_name NVARCHAR(256)
DECLARE @Command  NVARCHAR(1000)

set @schema_name = N'SchemaName'
set @table_name = N'TableName'
set @col_name = N'ColumnName'

SELECT @Command = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] DROP CONSTRAINT ' + d.name
 FROM sys.tables t   
  JOIN sys.default_constraints d       
   ON d.parent_object_id = t.object_id  
  JOIN sys.schemas s
        ON s.schema_id = t.schema_id
  JOIN    sys.columns c      
   ON c.object_id = t.object_id      
    AND c.column_id = d.parent_column_id
 WHERE t.name = @table_name
    AND s.name = @schema_name 
  AND c.name = @col_name

EXECUTE (@Command)

回答by user2346877

Drop all default contstraints in a database - safe for nvarchar(max) threshold.

删除数据库中的所有默认约束 - 对于 nvarchar(max) 阈值是安全的。

/* WARNING: THE SAMPLE BELOW; DROPS ALL THE DEFAULT CONSTRAINTS IN A DATABASE */ 
/* MAY 03, 2013 - BY WISEROOT  */
declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @df_name nvarchar(128)
declare @cmd nvarchar(128) 

declare table_names cursor for 
 SELECT t.name TableName, c.name ColumnName
 FROM sys.columns c INNER JOIN
     sys.tables t ON c.object_id = t.object_id INNER JOIN
     sys.schemas s ON t.schema_id = s.schema_id
     ORDER BY T.name, c.name

     open table_names
fetch next from table_names into @table_name , @column_name
while @@fetch_status = 0
BEGIN

if exists (SELECT top(1) 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_name and c.name = @column_name)
BEGIN
    SET @df_name = (SELECT top(1) 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_name and c.name = @column_name)
    select @cmd = 'ALTER TABLE [' + @table_name +  '] DROP CONSTRAINT [' +  @df_name + ']'
    print @cmd
    EXEC sp_executeSQL @cmd;
END

  fetch next from table_names into @table_name , @column_name
END

close table_names 
deallocate table_names

回答by Baked Inhalf

Run this command to browse all constraints:

运行此命令以浏览所有约束：

exec sp_helpconstraint 'mytable' --and look under constraint_name. 

It will look something like this: DF__Mytable__Column__[ABC123]. Then you can just drop the constraint.

它会是这个样子：DF__Mytable__Column__[ABC123]。然后你可以删除约束。

回答by Elnaz

I hope this could be helpful for whom has similar problem . In ObjectExplorerwindow, select your database=> Tables,=> your table=> Constraints. If the customer is defined on create column time, you can see the default name of constraint including the column name. then use:

我希望这对有类似问题的人有所帮助。在ObjectExplorer窗口中，选择您的数据库=> 表，=> 您的表=> 约束。如果客户在创建列时定义，您可以看到约束的默认名称，包括列名称。然后使用：

ALTER TABLE  yourTableName DROP CONSTRAINT DF__YourTa__NewCo__47127295;

(the constraint name is just an example)

（约束名称只是一个例子）

回答by Abdur Rahman

Following solution will drop specific default constraint of a column from the table

以下解决方案将从表中删除列的特定默认约束

Declare @Const NVARCHAR(256)

SET @Const = (
              SELECT TOP 1 'ALTER TABLE' + YOUR TABLE NAME +' DROP CONSTRAINT '+name
              FROM Sys.default_constraints A
              JOIN sysconstraints B on A.parent_object_id = B.id
              WHERE id = OBJECT_ID('YOUR TABLE NAME')
              AND COL_NAME(id, colid)='COLUMN NAME'
              AND OBJECTPROPERTY(constid,'IsDefaultCnst')=1
            )
 EXEC (@Const)

回答by user2832577

declare @table_name nvarchar(100)
declare @col_name nvarchar(100)
declare @constraint nvarchar(100)
set @table_name = N'TableName'
set @col_name = N'ColumnName'

IF EXISTS (select       c.*
    from        sys.columns c 
    inner join  sys.tables t on t.object_id = c.object_id
    where       t.name = @table_name
    and         c.name = @col_name) 
BEGIN

select @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_name
and c.name = @col_name

    IF LEN(ISNULL(@constraint, '')) <> 0
    BEGIN
        DECLARE @sqlcmd VARCHAR(MAX)
        SET @sqlcmd = 'ALTER TABLE ' + QUOTENAME(@table_name) + ' DROP CONSTRAINT' + 
        QUOTENAME(@constraint);
        EXEC (@sqlcmd);

    END

END
GO