[MSSQL] SQL Server FK (외래키) 설정

 

 

 

외래키 Foreign Key

외래키 생성 

 

-- 테이블 생성 시 FK 설정
CREATE TABLE 테이블명 (
컬럼 속성 
, CONSTRAINT FK명 FOREIGN KEY (FK설정할컬럼) REFERENCES 참조할 테이블(참조할 테이블의 컬럼) 옵션
) 

-- 기존 테이블 FK 설정
ALTER TABLE 테이블명 ADD CONSTRAINT FK명 FOREIGN KEY (FK설정할 컬럼) REFERENCES 참조할 테이블(참조할 테이블의 컬럼) 옵션

 

옵션

 

-- Master : 참조 대상 테이블 / Child : FK 생성 테이블

-- CASCADE : Master 삭제 시 Child 같이 삭제
CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) ON DELETE CASCADE
                    
-- SET NULL : Master 삭제 시 Child 해당 필드 NULL 값
CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) ON DELETE SET NULL
                    
-- SET DEFAULT : Master 삭제 시 Child 해당 필드 DEFAULT 값
CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) ON DELETE SET DEFAULT
                    
-- NO ACTION : 참조무결성 위반하는 액션은 되지 않음
CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) ON DELETE NO ACTION

 

1. 참조할 테이블의 컬럼은 PK거나 UNIQUE컬럼이어야 한다.

2. 외래키의 값은 참조할 테이블의 컬럼에 존재하는 값이어야 한다.

3. 참조 대상 컬럼의 값이 변경될 경우 설정된 옵션에 따라 외래키의 값 역시 바뀌게 된다.

 

자 이제 위의 설명한 3가지를 예제와 함께 자세히 살펴보자

 

 

 

1. 참조할 테이블의 컬럼은 PK거나 UNIQUE컬럼이어야 한다.

 

-- 기준 테이블
CREATE TABLE TEMP (ID INT)

-- 대상 테이블
CREATE TABLE TEMP2 (FID INT DEFAULT 100
                    , CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID))

 

위의 경우는 오류가 발생한다.

TEMP2 테이블에서 FID 컬럼을 FK로 설정하려고 한다.

이때 TEMP 테이블의 ID 컬럼을 참조 하려고 하는데, ID 값이 PK도 아니고 UNIQUE도 아니기 때문에 오류가 발생한다.

 

 

CREATE TABLE TEMP (ID INT PRIMARY KEY)
CREATE TABLE TEMP (ID INT UNIQUE)

 

이렇게 TEMP 테이블의 ID 값의 속성을 PK/UNIQUE로 설정할 경우 TEMP2가 정상적으로 생성된다.

 

 

 

 

2. 외래키의 값은 참조할 테이블의 컬럼에 존재하는 값이어야 한다.

 

-- 기준 테이블
CREATE TABLE TEMP (ID INT PRIMARY KEY)

-- 대상 테이블
CREATE TABLE TEMP2 (FID INT DEFAULT 100
                    , CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID))

INSERT INTO TEMP VALUES (1)
INSERT INTO TEMP VALUES (2)

INSERT INTO TEMP2 VALUES (1)
INSERT INTO TEMP2 VALUES (3) -- 오류발생
UPDATE TEMP2 SET FID = 3 WHERE FID = 1 -- 오류발생

 

TEMP2 테이블 FID값에 TEMP 테이블의 ID에 존재하지 않는 값 3을 넣으려고 하면 오류가 발생하게 된다.

 

 

 

 

 

3. 참조 대상 컬럼의 값이 변경될 경우 설정된 옵션에 따라 외래키의 값 역시 바뀌게 된다.

 

-- 기준 테이블
CREATE TABLE TEMP (ID INT PRIMARY KEY)

-- 대상 테이블
CREATE TABLE TEMP2 (FID INT DEFAULT 100
                    , CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) 옵션)
                    
INSERT INTO TEMP VALUES (1)
INSERT INTO TEMP VALUES (100)
INSERT INTO TEMP2 VALUES (1)

 

FK에 해당 옵션을 주고 다음 쿼리문을 실행했을 때 쿼리 실행 결과를 봐보자

 

 

1) UPDATE

 

UPDATE TEMP SET ID = 3 WHERE ID = 1

 

 

 

2) DELETE

 

DELETE FROM TEMP WHERE ID = 1