오라클 테이블, DELETE, UPDATE 시 참조 무결성 규칙, 외래키 데이터 삭제
참조무결성(외래키) 제약조건이 걸려 있는 경우 부모, 자식 테이블에서 가능한 DML을 정리했습니다.
부모테이블에서 삭제와 업데이트가 진행될 때 참조 무결성 규칙은 다음과 같습니다.
규칙 | 역할 |
Restrict(Default) | 부모테이블에서 행을 삭제 또는 수정하기 전에 자식테이블에서 부모테이블의 값을 참조하고 있으면 오류로 처리합니다. |
Set to Null | 부모 테이블에서 참조되는 값을 삭제나 업데이트할 때 모든 자식 테이블의 외래 키값을 NULL로 만듭니다. |
Set to Default | 부모 테이블에서 참조되는 값을 삭제나 업데이트할 때 모든 자식 테이블의 외래 키값을 기본값으로 만듭니다. |
Cascade | 부모 테이블에서 참조되는 값을 삭제할 때는 해당 값을 가지는 자식 레코드를 모두 삭제하고, 업데이트할 때는 해당 값으로 모두 변경합니다. |
No Action | 부모테이블에서 행을 삭제 또는 수정한 후 자식테이블에서 무결성 제약조건을 위배하지 않으면 그대로 두고 위배하면 오류로 처리 합니다. 요즘은 거의 사용하지 않습니다. |
앞에서 실습한 CUSTOMER, SALES 테이블에 입력된 데이터를 그대로 두고 아래 실습을 진행합니다. 부모/자식 관계에서 부모 테이블의 데이터 삭제시 참조무결성 규칙 ON DELETE SET DEFAULT, ON DELETE SET NULL, ON DELETE CASCASE를 테스트해 보겠습니다.
실습
부모테이블에서 삭제와 업데이트가 진행될 때 참조 무결성 규칙의 기본값은 RESTRICT 입니다. 부모테이블의 값이 수정 또는 삭제될 때 자식테이블에 그 값이 내려가 있으면 오류로 처리 합니다.
CUSTOMER 테이블에서 1번 고객을 삭제합니다. |
DELETE FROM CUSTOMER WHERE ID = 1; |
<실행결과>
ORA-02292: 무결성 제약조건(SCOTT.SYS_C007527)이 위배되었습니다- 자식 레코드가 발견되었습니다
자식 데이터가 있는 경우 부모 데이터를 삭제할 수 없기 때문에 에러가 발생했습니다.
실습
참조무결성 삭제 규칙중 부모테이블의 데이터가 삭제되면 자식테이블의 외래키 컬럼을 NULL로 변경하는 옵션을 지정하여 테이블을 다시 생성하고 이를 테스트 해보겠습니다.
SALES 테이블을 삭제 후 다시 생성합니다. 이때 부모 테이블(CUSTOMER)의 값이 삭제될 때 자식 테이블의 해당 값을 NULL로 바꾸는 ON DELETE SET NULL을 옵션을 사용합시다. |
DROP TABLE SALES; CREATE TABLE SALES ( ID NUMBER PRIMARY KEY, CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ON DELETE SET NULL, SALE_DATE DATE, AMOUNT NUMBER ); |
<실행결과>
Table SALES이(가) 생성되었습니다.
다시 부모 테이블인 CUSTOMER 테이블에서 1번 고객을 삭제합니다. |
DELETE FROM CUSTOMER WHERE ID = 1;
COMMIT;
<실행결과>
1 행 이(가) 삭제되었습니다.
이번에는 오류 없이 삭제가 됩니다.
자식테이블인 SALES 테이블의 외래키 컬럼(customer_id)의 값이 NULL로 변경된것을 확인합시다. |
SELECT * FROM SALES;
<실행결과>
| ID | CUSTOMER_ID | SALE_DATE | AMOUNT |
1 | 1 | (null) | 20/12/13 | 100000 |
자식테이블에서 CUSTOMER_ID 컬럼에 외래키 지정시 ON DELETE SET NULL 옵션을 사용했으므로 customer_id 컬럼의 값이 NULL로 변경됐습니다.
실습
이번에는 부모테이블 데이터가 삭제되면 자식테이블의 외래키 컬럼의 값을 기본값으로 변경하는 옵션인 ON DELETE SET DEFAULT를 사용해 보겠습니다.
SET DEFAULT절을 사용해 SALES SALES 테이블을 삭제 후 다시 생성합니다. |
CREATE TABLE SALES (
ID NUMBER PRIMARY KEY,
CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ON DELETE SET DEFAULT,
SALE_DATE DATE,
AMOUNT NUMBER
);
<실행결과>
ORA-03001: 현재에는 구현되어 있지 않은 기능입니다.
오라클19C에서 지원하지 않아 부모 테이블의 데이터가 삭제될 때 기본값로 설정하는 SET DEFAULT절을 사용할 수 없어 에러가 발생했습니다.
실습
ON DELETE CASCADE 옵션을 지정하면 부모데이터가 삭제될 때 그 자식들도 같이 삭제할 수 있습니다. CUSTOMER 테이블의 데이터를 다시 입력하고 SALES 테이블을 ON DELETE CASCADE 옵션을 기술하여 다시 생성하고 CUSTOMER 테이블의 데이터를 삭제하여 자식테이블 SALES도 같이 삭제 되는지 확인해 봅시다.
SALES 테이블을 삭제 후 다시 생성합니다. 부모 테이블의 데이터가 삭제될 때 자식 테이블도 같이 삭제되도록 설정 합시다. |
INSERT INTO CUSTOMER VALUES (1, '1길동');
DROP TABLE SALES;
CREATE TABLE SALES (
ID NUMBER PRIMARY KEY,
CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ON DELETE CASCADE,
SALE_DATE DATE,
AMOUNT NUMBER
);
INSERT INTO SALES VALUES (1, 1, SYSDATE, 100000);
<실행결과>
1 행 이(가) 삽입되었습니다.
Table SALES이(가) 삭제되었습니다.
Table SALES이(가) 생성되었습니다.
1 행 이(가) 삽입되었습니다.
SQL*Developerr에서 F5 키로 실습 쿼리를 한번에 실행 할 수 있습니다.
부모 테이블의 데이터를 삭제합니다. |
DELETE FROM CUSTOMER WHERE ID = 1;
COMMIT;
<실행결과>
1 행 이(가) 삭제되었습니다.
커밋 완료.
부모 데이터가 삭제될 때 자식 데이터도 같이 삭제됩니다. 진짜 그런지 확인해봅시다.
두 테이블을 SELECT로 조회해 봅시다. |
SELECT COUNT(*) FROM CUSTOMER;
<실행결과>
| COUNT(*) |
1 | 0 |
SELECT COUNT(*) FROM SALES;
<실행결과>
| COUNT(*) |
1 | 0 |
#참조무결성, #DELETECASCADE,#오라클, #테이블, #외래키, #ORACLE, #오라클교육, #오라클강좌
