美文网首页
How to drop a column with constr

How to drop a column with constr

作者: 蔡翔宇Leo | 来源:发表于2018-01-31 08:27 被阅读0次

    When we try to delete a column with a constraint, we need to delete the constraint first. But if the constraint is created by the db system, the constraint name will have a random hexadecimal suffix. Therefore, we should get the constraint name first. We can use the following SQL to get the constraints details.

    SELECT DEFAULT_CONSTRAINTS.NAME
    FROM SYS.ALL_COLUMNS, SYS.TABLES, SYS.DEFAULT_CONSTRAINTS
    WHERE ALL_COLUMNS.OBJECT_ID = TABLES.OBJECT_ID
          AND ALL_COLUMNS.DEFAULT_OBJECT_ID = DEFAULT_CONSTRAINTS.OBJECT_ID
    
    query result

    Therefore, we can use this way to get the name of the constraint which is related to the target column first. Then save it as a variable, and delete the constraint and the column.
    The complete codes are as follows:

    IF COL_LENGTH('TABLE_NAME', 'COLUMN_NAME') IS NOT NULL
      BEGIN
        DECLARE @CONSTR_NAME VARCHAR(MAX)
        SET @CONSTR_NAME = (
          SELECT DEFAULT_CONSTRAINTS.NAME
          FROM SYS.ALL_COLUMNS, SYS.TABLES, SYS.DEFAULT_CONSTRAINTS
          WHERE ALL_COLUMNS.OBJECT_ID = TABLES.OBJECT_ID
                AND ALL_COLUMNS.DEFAULT_OBJECT_ID = DEFAULT_CONSTRAINTS.OBJECT_ID AND TABLES.NAME = 'TABLE_NAME'
                AND ALL_COLUMNS.NAME = 'COLUMN_NAME')
        IF @CONSTR_NAME IS NOT NULL
          BEGIN
            DECLARE @STATEMENT VARCHAR(MAX)
            SET @STATEMENT = 'ALTER TABLE TABLE_NAME DROP ' + @CONSTR_NAME
            EXEC (@STATEMENT)
          END
    
        ALTER TABLE Scripts
          DROP COLUMN COLUMN_NAME
      END
    GO
    

    相关文章

      网友评论

          本文标题:How to drop a column with constr

          本文链接:https://www.haomeiwen.com/subject/sgtkzxtx.html