[sql] 외래 키 제약 조건이 SQL Server에있는 경우에만 어떻게 삭제합니까?

Program / 글쓴이

다음 코드를 사용하여 테이블이 존재하는 경우 테이블을 삭제할 수 있지만 제약 조건으로 동일한 작업을 수행하는 방법을 모릅니다.

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName
go

이 코드를 사용하여 제약 조건을 추가합니다.

ALTER TABLE [dbo].[TableName] 
  WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
    REFERENCES [dbo].[TableName2] ([ID])
go

답변

Eric Isaacs의 답변 에서 더 간단한 해결책이 제공됩니다 . 그러나 모든 테이블에서 제약 조건을 찾습니다. 특정 테이블에서 외래 키 제약 조건을 대상으로하려면 다음을 사용하십시오.

IF EXISTS (SELECT *
  FROM sys.foreign_keys
   WHERE object_id = OBJECT_ID(N'dbo.FK_TableName_TableName2')
   AND parent_object_id = OBJECT_ID(N'dbo.TableName')
)
  ALTER TABLE [dbo.TableName] DROP CONSTRAINT [FK_TableName_TableName2]

답변

이것은 현재 제안 된 솔루션보다 훨씬 간단합니다.

IF (OBJECT_ID('dbo.FK_ConstraintName', 'F') IS NOT NULL)
BEGIN
    ALTER TABLE dbo.TableName DROP CONSTRAINT FK_ConstraintName
END

다른 유형의 제약 조건을 삭제해야하는 경우 두 번째 매개 변수 위치에서 OBJECT_ID () 함수에 전달할 수있는 해당 코드는 다음과 같습니다.

C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
UQ = UNIQUE constraint

두 번째 매개 변수없이 OBJECT_ID를 사용할 수도 있습니다.

전체 유형 목록은 다음과 같습니다.

객체 유형 :

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object

적용 대상 : SQL Server 2012에서 SQL Server 2014까지

SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

답변

SQL Server 2016에서는 DROP IF EXISTS를 사용할 수 있습니다.

CREATE TABLE t(id int primary key,
               parentid int
                    constraint tpartnt foreign key references t(id))
GO
ALTER TABLE t
DROP CONSTRAINT IF EXISTS tpartnt
GO
DROP TABLE IF EXISTS t

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx 참조

답변

IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
BEGIN
    ALTER TABLE [dbo].[tableName]
    DROP CONSTRAINT DF_Constraint
END

답변

James의 답변 은 실제 제약 조건의 이름을 알고 있으면 잘 작동합니다. 까다로운 점은 레거시 및 기타 실제 시나리오 에서는 제약 조건이 무엇인지 알 수 없다는 것 입니다.

이 경우 중복 구속 조건을 작성할 위험이 있으므로 다음을 사용할 수 있습니다. 

create function fnGetForeignKeyName
(
    @ParentTableName nvarchar(255),
    @ParentColumnName nvarchar(255),
    @ReferencedTableName nvarchar(255),
    @ReferencedColumnName nvarchar(255)
)
returns nvarchar(255)
as
begin
    declare @name nvarchar(255)

    select @name = fk.name  from sys.foreign_key_columns fc
    join sys.columns pc on pc.column_id = parent_column_id and parent_object_id = pc.object_id
    join sys.columns rc on rc.column_id = referenced_column_id and referenced_object_id = rc.object_id
    join sys.objects po on po.object_id = pc.object_id
    join sys.objects ro on ro.object_id = rc.object_id
    join sys.foreign_keys fk on fk.object_id = fc.constraint_object_id
    where
        po.object_id = object_id(@ParentTableName) and
        ro.object_id = object_id(@ReferencedTableName) and
        pc.name = @ParentColumnName and
        rc.name = @ReferencedColumnName

    return @name
end

go

declare @name nvarchar(255)
declare @sql nvarchar(4000)
-- hunt for the constraint name on 'Badges.BadgeReasonTypeId' table refs the 'BadgeReasonTypes.Id'
select @name = dbo.fnGetForeignKeyName('dbo.Badges', 'BadgeReasonTypeId', 'dbo.BadgeReasonTypes', 'Id')
-- if we find it, the name will not be null
if @name is not null
begin
    set @sql = 'alter table Badges drop constraint ' + replace(@name,']', ']]')
    exec (@sql)
end

답변

ALTER TABLE [dbo].[TableName]
    DROP CONSTRAINT FK_TableName_TableName2

답변

Declare @FKeyRemoveQuery NVarchar(max)

IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))

BEGIN
    SELECT @FKeyRemoveQuery='ALTER TABLE dbo.TableName DROP CONSTRAINT [' + LTRIM(RTRIM([name])) + ']'
    FROM sys.foreign_keys
    WHERE parent_object_id = OBJECT_ID(N'dbo.TableName')

    EXECUTE Sp_executesql @FKeyRemoveQuery

END