如何删除 T-SQL 中的默认值或类似约束？

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

提问by Frank Krueger

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

但是当我不知道它的名字时如何删除默认约束？（也就是说，它是在CREATE TABLE时间自动生成的。）

采纳答案by adrianbanks

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

如果您想手动执行此操作，您可以使用 Management Studio 来查找它（在表内的 Constraints 节点下）。

To do it using SQL:

要使用 SQL 执行此操作：

• If the constraints are default constraints, you can use sys.default_constraintsto 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
  

• 如果约束是默认约束，您可以使用sys.default_constraints它来查找：

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

• 如果您还在寻找其他约束（检查、唯一、外键、默认、主键），您可以使用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.

您没有说明您使用的是哪个版本的 SQL Server。以上在 SQL 2005 和 SQL 2008 上都适用。

回答by Polemarch

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.

只需更换<MYTABLENAME>和<MYCOLUMNNAME>适当。

回答by Alex_L

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

或者您可以使用 sys.check_constraints 目录视图找到它。

回答by Tetraneutron

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

您可以通过 sp_help [table name] 找到约束的名称，然后按名称删除它。

Or you can probably do this via Management studio.

或者你可以通过管理工作室来做到这一点。

回答by sweetfa

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.

查看 information_schema 中可用的其他列，以便您进一步区分。

回答by Piotr Findeisen

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)

这是我自己的版本，它删除了所有依赖约束——默认约束（如果存在）和所有受影响的检查约束（正如 SQL 标准似乎建议的那样，而其他一些数据库似乎也是如此）

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 TableNameand ColumnToBeDroppedappear twice in the code above)

（注意：在上面的代码中两者TableName和ColumnToBeDropped出现两次）

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

这通过构造 singleALTER TABLE TableName DROP CONSTRAINT c1, ..., COLUMN ColumnToBeDropped并执行它来工作。