在 SQL Server 中删除具有默认约束的列（如果存在）

Drop a column with a default constraint in SQL Server (IF EXISTS)

提问by user1263981

I am writing a sql script for dropping column and a default constraint. The following script works fine but i like to know if it is a right way of doing it.

我正在编写一个用于删除列和默认约束的 sql 脚本。以下脚本工作正常，但我想知道这是否是正确的做法。

Can i drop a default constraint with a column in one statement instead of using two separate ones?

我可以在一个语句中删除带有一列的默认约束，而不是使用两个单独的约束吗？

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D')

BEGIN    
   ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID]    
END    
GO    
BEGIN   
  ALTER TABLE [dbo].[Employees] DROP COLUMN [EmpID]    
END

回答by warren.sentient

In SQL Server 2005 upwards you can drop both the constraint and the column in one statement.

在 SQL Server 2005 以上的版本中，您可以在一个语句中同时删除约束和列。

The syntax is

语法是

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ]

The emphasis is on [ ,...n ], indicating multiple terms.

重点是[ ,...n ]，表示多个术语。

NB! Since the terms are processed sequentially, if the column being dropped is part of the constraint being dropped, then the constraint must be the first term, followed by the column term.

注意！由于项是按顺序处理的，如果要删除的列是要删除的约束的一部分，则约束必须是第一个项，然后是列项。

In your example:

在你的例子中：

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]

So your code would be:

所以你的代码是：

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D')
BEGIN
   ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]
END
GO

In SQL Server 2016 they have introduced the IF EXISTS clause which removes the need to check for the existence of the constraint first e.g.

在 SQL Server 2016 中，他们引入了 IF EXISTS 子句，无需首先检查约束是否存在，例如

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT IF EXISTS [DF_Employees_EmpID], COLUMN IF EXISTS [EmpID]

回答by Catto

Here is another way to drop a column & default constraintswith checking if they exist before dropping them:

这是删除列和默认约束的另一种方法，在删除它们之前检查它们是否存在：

-------------------------------------------------------------------------
-- Drop COLUMN 
-- Name of Column: Column_EmployeeName 
-- Name of Table: table_Emplyee 
--------------------------------------------------------------------------
IF EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'table_Emplyee'
              AND COLUMN_NAME = 'Column_EmployeeName'
           )
    BEGIN 

        IF EXISTS ( SELECT 1 
                    FROM sys.default_constraints 
                    WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]') 
                      AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
                  )
            BEGIN 
                ------  DROP Contraint

                ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
            PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'  
            END
     --    -----   DROP Column   -----------------------------------------------------------------    
        ALTER TABLE [dbo].table_Emplyee 
            DROP COLUMN Column_EmployeeName
        PRINT 'Column Column_EmployeeName in images table was dropped'    
    END  

--------------------------------------------------------------------------
-- ADD  COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
                SELECT 1
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'table_Emplyee'
                  AND COLUMN_NAME = 'Column_EmployeeName'
               )
    BEGIN  
    ----- ADD Column & Contraint               
        ALTER TABLE dbo.table_Emplyee 
            ADD Column_EmployeeName BIT   NOT NULL 
            CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]  DEFAULT (0) 
        PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added' 
        PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'      
     END

GO 

回答by Martin Smith

How you have it is fine.

你如何拥有它很好。

An alternative would be

另一种选择是

IF OBJECT_ID('DF_Employees_EmpID', 'D') IS NULL
  BEGIN
      ALTER TABLE dbo.Employees
        DROP COLUMN EmpID
  END
ELSE
  BEGIN
      ALTER TABLE dbo.Employees
        DROP CONSTRAINT DF_Employees_EmpID, 
                 COLUMN EmpID 
  END 

In the event that the constraint does exist this combines the two operations into a single statement/transaction.

在约束确实存在的情况下，这将两个操作组合成单个语句/事务。

回答by Sajjan Sarkar

Another solution:

另一种解决方案：

DECLARE @TableName sysname,
        @Schema sysname,
        @colname sysname,
        @sql VARCHAR(1000)

SELECT @Schema = 'dbo',
       @TableName = 'mytable',
       @colname = 'mycol'


IF COL_LENGTH(@Schema+'.'+@TableName, @colname) IS NULL
BEGIN
    PRINT 'Column does not exist!'
END
ELSE
BEGIN
    SET @sql = ''
    SELECT @sql += N' ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + default_constraints.name + ';'
    FROM sys.all_columns
        INNER JOIN sys.tables
            ON all_columns.object_id = TABLES.object_id
        INNER JOIN sys.schemas
            ON TABLES.schema_id = schemas.schema_id
        INNER JOIN sys.default_constraints
            ON all_columns.default_object_id = default_constraints.object_id
    WHERE schemas.name = @Schema
          AND tables.name = @TableName
          AND all_columns.name = @colname


    SET @sql += N' ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @colname + ';'




    PRINT ISNULL(@sql, 'NULL')

       EXECUTE(@sql)


END