[Oracle] 오라클 컬럼 외래키(Foreign Key) 추가, 삭제 방법

오라클에서 테이블 간의 관계를 설정하고, 데이터의 참조 무결성을 유지하고 싶을 때는 FOREIGN KEY 제약조건을 사용한다. ALTER TABLE 명령어를 통해 기존 테이블에도 외래키 제약조건을 추가하거나 삭제할 수 있으며, 부모 테이블의 값을 참조하도록 자식 테이블의 컬럼을 제한할 수 있다. FOREIGN KEY 제약조건이 추가된 이후에는, 존재하지 않는 값을 INSERT 하거나, 참조된 부모 데이터를 DELETE 하려고 할 경우 오류가 발생한다.

 

목차 외래키 제약조건 추가 방법 외래키 제약조건 활성화/비활성화 방법 외래키 제약조건 삭제 방법 테이블 생성시 외래키 제약조건 추가 방법 외래키 다양한 옵션 사용방법

 

외래키 제약조건 추가 방법

-- 외래키 제약조건 추가
ALTER TABLE scott.emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) 
REFERENCES scott.dept(deptno);

 

ALTER TABLE [스키마명].[테이블명] ADD CONSTRAINT [외래키명] FOREIGN KEY ([컬럼], [컬럼] ...)

REFERENCES [스키마명].[테이블명]([컬럼], [컬럼] ...)

 

REFERENCES로 참조하는 테이블(scott.dept)의 칼럼(deptno)은 기본키(PK)나 유니크키(UK) 여야만 외래키를 생성할 수 있다.

 

 

외래키 제약조건 활성화/비활성화 방법

-- 외래키 비활성화
ALTER TABLE scott.emp DISABLE CONSTRAINT fk_emp_dept;

-- 외래키 활성화
ALTER TABLE scott.emp ENABLE CONSTRAINT fk_emp_dept;

 

외래키를 비활성화하면 부모-자식 관계 무결성 검사가 일시적으로 해제되기 때문에, 대량 데이터를 입력·수정하는 경우에 유용하게 활용할 수 있다.

 

외래키 활성화 시점에 무결성을 위배하는 데이터가 있다면 오류(ORA-02298)가 발생한다. 반드시 활성화 전에 무결성을 확인해야 한다.

 

외래키 제약조건 삭제 방법

-- 외래키 제약조건 삭제
ALTER TABLE scott.emp DROP CONSTRAINT fk_emp_dept;

 

ALTER TABLE [스키마명]. [테이블명]  DROP CONSTRAINT [외래키명];

 

테이블 생성시 외래키 제약조건 추가 방법

CREATE TABLE scott.emp 
( 
    empno       NUMBER(4)	NOT NULL,
    ename       VARCHAR2(10),
    job         VARCHAR2(9),
    mgr         NUMBER(4),
    hiredate    DATE,
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(2),	
    CONSTRAINT pk_emp PRIMARY KEY (empno), 
    -- 외래키 제약조건 추가
    CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) 
    REFERENCES scott.dept(deptno)
);

 

CREATE TABLE 구문에 CONSTRAINT [외래키명] FOREIGN KEY([컬럼]) REFERENCES [부모테이블명]([컬럼])을 추가하여 테이블 생성 시 외래키도 같이 추가되도록 할 수 있다.

 

외래키 다양한 옵션 사용방법

-- RESTRICT (기본값): 부모 데이터가 참조되면 삭제 불가
ALTER TABLE scott.emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) 
REFERENCES scott.dept(deptno);

-- CASCADE: 부모 삭제 시 자식도 함께 삭제
ALTER TABLE scott.emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) 
REFERENCES scott.dept(deptno) ON DELETE CASCADE;

-- SET NULL: 부모 삭제 시 자식의 외래키를 NULL로 설정
ALTER TABLE scott.emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) 
REFERENCES scott.dept(deptno) ON DELETE SET NULL;

 

ON DELETE CASCADE: 부모 테이블에서 행이 삭제되면 자식 테이블에서도 자동으로 삭제
ON DELETE SET NULL: 부모 테이블에서 행이 삭제되면 자식 테이블의 외래키 값을 NULL로 설정

 

-- DML 실행 시점에 검증 (기본값), INITIALLY IMMEDIATE 생략가능
ALTER TABLE scott.emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) 
REFERENCES scott.dept(deptno) INITIALLY IMMEDIATE;

-- COMMIT 시점에 제약조건 위배 여부를 검증
ALTER TABLE scott.emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) 
REFERENCES scott.dept(deptno) DEFERRABLE INITIALLY IMMEDIATE;

 

기본적으로  INSERT, UPDATE 할 때 바로 제약조건 위배 여부를 검사하지만, DEFERRABLE 옵션을 사용하면 트랜잭션 종료 시점에만 최종적으로 검사를 한다.