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

Question

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

我能否在一个语句中同时删除一列和默认约束，而不是使用两个单独的语句？

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

- user1263981

---

我原以为你必须分别执行它们 - 如果需要条件检查，确实是这样。如果约束肯定存在，则可以将其作为单个ALTER TABLE完成。 - Damien_The_Unbeliever

我做的方式正确吗？ - user1263981

是的，如果你需要条件检查，没有真正缩短这段代码的方法。 - Damien_The_Unbeliever

Answer 1

在SQL Server 2005及以上版本中，您可以在一条语句中删除约束和列。语法为：

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

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

NB！由于术语是按顺序处理的，如果要删除的列是约束的一部分，则约束必须是第一个术语，其后是列术语。

以您的示例为例：

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

那么你的代码应该是：

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

在SQL Server 2016中，引入了IF EXISTS子句，它消除了首先检查约束是否存在的需要，例如：

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

- warren.sentient

Answer 2

以下是另一种方法，用于删除列和默认约束，并在删除之前检查它们是否存在：

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

- Catto

Answer 3

另一种解决方案：

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

- Sajjan Sarkar

Answer 4

你现在的做法很好。

另一种选择是

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 

如果约束存在，则将这两个操作合并为单个语句/事务。

- Martin Smith