[sql-server] 제약 조건으로 열을 삭제하는 방법은 무엇입니까?

SQL Server 2008에서 기본 제약 조건이있는 열을 삭제하는 방법은 무엇입니까?

내 질문은

alter table tbloffers
drop column checkin

오류가 발생합니다

하나 이상의 오브젝트가이 컬럼에 액세스하므로 ALTER TABLE DROP COLUMN 체크인에 실패했습니다.

누구나 내 쿼리를 수정하여 제약 조건이있는 열을 삭제할 수 있습니까?

답변

먼저 문제가있는을 삭제해야합니다. DEFAULT constraint그 후에 열을 삭제할 수 있습니다

alter table tbloffers drop constraint [ConstraintName]
go

alter table tbloffers drop column checkin

그러나 다른 이유로 인해 오류가 나타날 수 있습니다 (예 : 사용자 정의 함수 또는 SCHEMABINDING옵션이 설정된 보기) .

UPD :
완전 자동 구속 조건 제거 스크립트 :

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'alter table tbloffers drop constraint ['+dc.NAME+N']'
    from sys.default_constraints dc
    JOIN sys.columns c
        ON c.default_object_id = dc.object_id
    WHERE
        dc.parent_object_id = OBJECT_ID('tbloffers')
    AND c.name = N'checkin'
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

답변

제약 조건 이름을 얻기 위해 먼저 별도의 쿼리를 실행하지 않고도 알 수없는 이름으로 기본 제약 조건을 삭제하는 또 다른 방법이 있습니다.

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 = N'__ColumnName__'
                        AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

답변

열과 제약 조건을 개별적으로가 아니라 단일 문으로 삭제할 수도 있습니다.

CREATE TABLE #T
  (
     Col1 INT CONSTRAINT UQ UNIQUE CONSTRAINT CK CHECK (Col1 > 5),
     Col2 INT
  )

ALTER TABLE #T DROP CONSTRAINT UQ ,
                    CONSTRAINT CK,
                    COLUMN Col1


DROP TABLE #T

종속 검사 제한 조건 및 기본 제한 조건의 이름을 찾아서 열과 함께 삭제하는 일부 동적 SQL은 다음과 같습니다.

(단, 외래 키, 고유 및 기본 키 제약 조건, 계산 열, 인덱스와 같은 다른 가능한 열 종속성은 아님)

CREATE TABLE [dbo].[TestTable]
(
A INT DEFAULT '1' CHECK (A=1),
B INT,
CHECK (A > B)
)

GO

DECLARE @TwoPartTableNameQuoted nvarchar(500) = '[dbo].[TestTable]',
        @ColumnNameUnQuoted sysname = 'A',
        @DynSQL NVARCHAR(MAX);

SELECT @DynSQL =
     'ALTER TABLE ' + @TwoPartTableNameQuoted + ' DROP' +
      ISNULL(' CONSTRAINT ' + QUOTENAME(OBJECT_NAME(c.default_object_id)) + ',','') +
      ISNULL(check_constraints,'') +
      '  COLUMN ' + QUOTENAME(@ColumnNameUnQuoted)
FROM   sys.columns c
       CROSS APPLY (SELECT ' CONSTRAINT ' + QUOTENAME(OBJECT_NAME(referencing_id)) + ','
                    FROM   sys.sql_expression_dependencies
                    WHERE  referenced_id = c.object_id
                           AND referenced_minor_id = c.column_id
                           AND OBJECTPROPERTYEX(referencing_id, 'BaseType') = 'C'
                    FOR XML PATH('')) ck(check_constraints)
WHERE  c.object_id = object_id(@TwoPartTableNameQuoted)
       AND c.name = @ColumnNameUnQuoted;

PRINT @DynSQL;
EXEC (@DynSQL);

답변

이 쿼리에서 기본 제약 조건을 찾으십시오.

SELECT
    df.name 'Constraint Name' ,
    t.name 'Table Name',
    c.NAME 'Column Name'
FROM sys.default_constraints df
INNER JOIN sys.tables t ON df.parent_object_id = t.object_id
INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id

기본 제한 조건의 이름과 테이블 및 열 이름이 제공됩니다.

해당 정보가 있으면 먼저 기본 제한 조건을 삭제해야합니다.

ALTER TABLE dbo.YourTable
DROP CONSTRAINT name-of-the-default-constraint-here

그런 다음 열을 삭제할 수 있습니다

ALTER TABLE dbo.YourTable DROP COLUMN YourColumn

답변

다음은 SQL Azure 백엔드 (SQL Server Management Studio 사용), YMMV에 대해 나에게 도움이되었지만 그것이 효과가 있다면 다른 솔루션보다 훨씬 간단합니다.

ALTER TABLE MyTable
    DROP CONSTRAINT FK_MyColumn
    CONSTRAINT DK_MyColumn
    -- etc...
    COLUMN MyColumn
GO

답변

나는 같은 것을 얻었다 :

하나 이상의 객체가이 열 메시지에 액세스하기 때문에 ALTER TABLE DROP COLUMN이 실패했습니다 .

내 열에 인덱스를 먼저 삭제해야했습니다. sys.indexes를 사용 하여 트릭을 수행했습니다.

DECLARE @sql VARCHAR(max)

SELECT @sql = 'DROP INDEX ' + idx.NAME + ' ON tblName'
FROM sys.indexes idx
INNER JOIN sys.tables tbl ON idx.object_id = tbl.object_id
INNER JOIN sys.index_columns idxCol ON idx.index_id = idxCol.index_id
INNER JOIN sys.columns col ON idxCol.column_id = col.column_id
WHERE idx.type <> 0
    AND tbl.NAME = 'tblName'
    AND col.NAME = 'colName'

EXEC sp_executeSql @sql
GO

ALTER TABLE tblName
DROP COLUMN colName

답변

스크립트를 SQL Server 버전으로 약간 업데이트했습니다.

DECLARE @sql nvarchar(max)

SELECT @sql = 'ALTER TABLE `table_name` DROP CONSTRAINT ' + df.NAME
FROM sys.default_constraints df
  INNER JOIN sys.tables t ON df.parent_object_id = t.object_id
  INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id
where t.name = 'table_name' and c.name = 'column_name'

EXEC sp_executeSql @sql
GO

ALTER TABLE table_name
  DROP COLUMN column_name;