[DATABASE/SQLSERVER] FK 생성문 만들기

icodebroker SQLSERVER 2024-06-18

■ FK 생성문을 만드는 방법을 보여준다.

▶ 예제 코드 (SQL)


SELECT
    'ALTER TABLE ' + OBJECT_NAME(FK.parent_object_id) + ' ' +
    'ADD CONSTRAINT ' + '[' + FK.name + '] ' +
    'FOREIGN KEY ' + '(' + COL_NAME(FK.parent_object_id, FC.parent_column_id) + ') ' +
    'REFERENCES ' + '[' + TB.name + '] ' + '(' + COL_NAME(FK.referenced_object_id, FC.referenced_column_id) + ')'
FROM       sys.foreign_keys        AS FK
INNER JOIN sys.foreign_key_columns AS FC ON FC.constraint_object_id = FK.[object_id]
INNER JOIN sys.tables              AS TB ON TB.[object_id]          = FK.referenced_object_id;

1

2

3

4

5

6

7

8

9

10

SELECT

'ALTER TABLE ' + OBJECT_NAME(FK.parent_object_id) + ' ' +

'ADD CONSTRAINT ' + '[' + FK.name + '] ' +

'FOREIGN KEY ' + '(' + COL_NAME(FK.parent_object_id, FC.parent_column_id) + ') ' +

'REFERENCES ' + '[' + TB.name + '] ' + '(' + COL_NAME(FK.referenced_object_id, FC.referenced_column_id) + ')'

FROM sys.foreign_keys AS FK

INNER JOIN sys.foreign_key_columns AS FC ON FC.constraint_object_id = FK.[object_id]

INNER JOIN sys.tables AS TB ON TB.[object_id] = FK.referenced_object_id;