참조키 확인및 삭제

-- 참조키확인

 

SELECT

    FK_Table  = FK.TABLE_NAME,

    FK_Column = CU.COLUMN_NAME,

    PK_Table  = PK.TABLE_NAME,

    PK_Column = PT.COLUMN_NAME,

    Constraint_Name = C.CONSTRAINT_NAME

FROM

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

    INNER JOIN

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

    INNER JOIN

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK

        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

    INNER JOIN

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU

        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

    INNER JOIN

    (

        SELECT

            i1.TABLE_NAME, i2.COLUMN_NAME

        FROM

            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

            INNER JOIN

            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2

            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'

    ) PT

    ON PT.TABLE_NAME = PK.TABLE_NAME

-- optional:

WHERE PK.TABLE_NAME = 'TableName'

ORDER BY

    1,2,3,4

   

   

   

-- 참조키삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름