오라클 테이블, 외래 키 제약조건(Foreign Key Constraints)
외래 키foreign key는 테이블의 컬럼값이 자신 또는 다른 테이블의 기본 키와 고유 키 값만 오도록 할 때 지정하는 제약조건입니다. 따라서 외래 키로 지정할 때 참조되는 테이블 컬럼은 기본 키 또는 고유 키이어야 합니다. 물론 외래키 컬럼은 NULL을 허용할 수 있으며 이 경우 NULL 값은 입력될 수 있습니다. 즉 외래키는 값이 없는것(NULL)은 이해 하지만 값이 들어 올때는 부모 테이블(참조되는 테이블)의 기본키, 고유키 값만 들어 올 수 있다고 정의하는 것 입니다.
사원테이블emp의 deptno 컬럼은 NULL을 허용하면서 부서테이블dept의 PK 컬럼(deptno)을 참조하는 외래키로 NULL 값은 입력 될 수 있지만 값이 들어 온다면 부서 테이블의 기본키인 deptno 값 중에서만 들어와야 한다는 것입니다. 이때 사원테이블이 자식, 부서테이블이 부모 테이블이 되며 사원테이블의 deptno 컬럼은 외래키foreign key, 부서테이블의 deptno 컬럼은 참조키referencial key라고 합니다.
외래키와 참조키가 하나의 테이블에 존재할 수 있는데 이를 자기참조(Self Reference)라고 하고 이 경우 부모 테이블과 자식 테이블은 동일합니다. 예를 들어 사원테이블에서 관리자(mgr) 컬럼은 외래 키 컬럼으로 자기자신 테이블(EMP)의 사번(empno) 컬럼을 참조합니다. 이러한 구조를 가진 경우 관리자의 이름을 알려면 관리자의 사번으로 사원 테이블을 검색해야 하므로 셀프 조인self jon이 자주 사용됩니다.
사용자가 작성한 USER_CONSTRAINTS 뷰를 통해 외래키 제약조건들을 조회하고 외래키를 삭제 후 다시 생성하는 실습을 해보겠습니다.
실습
USER_CONSTRAINTS 딕셔너리 뷰를 통해 사용자가 생성한 제약조건을 조회할 수 있습니다.
사원(EMP) 테이블의 제약조건을 조회 합시다. |
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP';
<실행결과>
| CONSTRAINT_NAME | CONSTRAINT_TYPE |
1 | FK_DEPTNO | R |
2 | FK_MGR | R |
3 | PK_EMP | P |
R은 참조무결성, 외래 키를 의미하고, P는 기본 키를 의미합니다. EMP 테이블에서 deptno 컬럼은 외래키로 DEPT 테이블의 deptno 컬럼을 참조하고, mgr 컬럼은 자기자신 테이블인 EMP 테이블의 empno 컬럼을 참조합니다.
부서(DEPT) 테이블의 제약조건을 조회 합시다. |
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT';
<실행결과>
| CONSTRAINT_NAME | CONSTRAINT_TYPE |
1 | PK_DEPT | P |
실습
EMP 테이블에서 외래키를 삭제해보고 다시 만들어 보겠습니다.
사원(EMP) 테이블의 deptno 컬럼에 걸려있는 외래 키를 삭제 후 다시 생성하세요 외래키 가끔은 외래키 제약조건을 비활성화, 활성화, 삭제 하는 경우도 있으므로 생성시 이름을 부여하여 생성하세요. |
ALTER TABLE emp DROP CONSTRAINT fk_deptno; ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno); |
실습
실습을 위해 고객과 매출 테이블을 생성하고 데이터를 입력 합시다.
고객(CUSTOMER), 매출(SALES) 테이블을 생성합니다. 1:N 관계로 CUSTOMER가 부모 테이블, SALES가 자식 테이블입니다. |
DROP TABLE CUSTOMER;
DROP TABLE SALES;
CREATE TABLE CUSTOMER (
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL
);
CREATE TABLE SALES (
ID NUMBER PRIMARY KEY,
CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ,
SALE_DATE DATE,
AMOUNT NUMBER
);
데이터 입력은 부모 테이블부터 입력해야 합니다. 자식 테이블 SALES는 기본 키 컬럼인 id가 부모 테이블의 id 컬럼을 참조하므로 부모 테이블인 CUSTOMER 없이 SALES 데이터가 만들어질 수 없습니다.
부모테이블부터 데이터를 입력 합시다. |
INSERT INTO CUSTOMER VALUES (1, '1길동');
INSERT INTO SALES VALUES (1, 1, SYSDATE, 100000);
COMMIT;
<실행결과>
커밋 완료.
실습
참조무결성을 테스트 하기 위해 부모테이블(CUSTOMER)에 없는 id 값으로 자식테이블(SALES)에 입력을 시도해 보겠습니다.
CUSTOMER에 없는 id ‘2’ 로 SALES 테이블에 새행을 입력합시다. 무결성 제약조건 오류가 발생합니다. |
INSERT INTO SALES VALUES (2, 2,SYSDATE, 100000);
<실행결과>
ORA-02291: 무결성 제약조건(SCOTT.SYS_C007527)이 위배되었습니다- 부모 키가 없습니다.
#외래키, #ForeignKey, #오라클케이블, #테이블, #오라클강좌, #오라클교육
