Foreign Key 제약 설정 Delete Rule

Foreign Key 제약 설정시 On delete cascade 구문에 대해 헤깔리는 부분이 있어서
정리하였습니다. 테스트한 결과입니다.
 
 
1. 테이블 생성
-- 부모 테이블
create table p1
(
no number(10) not null primary key,
name varchar2(10)
);
-- 자식 : 기본키 속성
create table p2
(
no number(10) not null primary key,
name varchar2(10)
);
-- 자식 : 일반속성
create table p3
(
id number(10) not null primary key,
no number(10)
);

2. 데이터 Insert
insert into P1
select level no ,'A' from dual connect by level <=3;
commit;
insert into P2
select level no ,'A' from dual connect by level <=3;
commit;

insert into P3
select level no , level  from dual connect by level <=3;
commit;

3.제약조건 설정 및 테스트
1) Case 1
alter table p2 add constraint fk_p2 foreign key(no) references p1(no);
alter table p3 add constraint fk_p3 foreign key(no) references p1(no);

- 부모 테이블에서 데이터 삭제시 Default로 생성되는 옵션 "NO ACTION"
- 명시적으로 프로그램에서 자식 테이블의 Row 모두 삭제하고 부모 테이블의 Row를 삭제해야됨

2) Case 2
alter table p2 add constraint fk_p2 foreign key(no) references p1(no) on delete cascade;
alter table p3 add constraint fk_p3 foreign key(no) references p1(no) on delete cascade;
- 부모 테이블에서 데이터 삭제시 자식 테이블의 Row는 자동으로 삭제됨
3) Case 3
alter table p2 add constraint fk_p2 foreign key(no) references p1(no) on delete set null;  ---- PK는 SET NULL 사용불가
alter table p3 add constraint fk_p3 foreign key(no) references p1(no) on delete set null;
- 부모 테이블에서 데이터 삭제시 자식 테이블의 Row는 자동으로 NULL로 업데이트 함
- 기본키 속성에는 위배됨.(Not Null 제약에 의해서)
* 따라서 Foreign Key 제약 조건에 의해 데이터 무결성을 유지하기 위한 방법으로
  -Foreign Key 설정시 On Delete 구문을 추가하지 않으면 Default 옵션은  "No Action"
   .명시적으로 프로그램에서 자식 테이블의 Row를 삭제한후 부모 테이블의 Row를 삭제할수 있음
  -DB에서 자동으로 데이터를 처리할 경우 기본키는 "On Delete cascade", 일반속성은 "On Delete set null"
   를 사용해야 됨.

4.결론
 
  -부모 테이블의 Row에 대해서는 자동으로 자식테이블 까지 삭제 가능(Delete or Set NULL)
  -업데이트는 프로그램을 통해서 명시적으로 구현해야됨
  
* Oracle은 ON UPDATE 구문 사용이 불가능함. 트리거를 이용해서 구현해야 됨
* Syntax 
REFERENCES tableName [ ( simpleColumnName [ , simpleColumnName ]* ) ]
[ ON DELETE { NO ACTION | RESTRICT | CASCADE | SET NULL } ]
   [ ON UPDATE { NO ACTION | RESTRICT } ]
|
[ ON UPDATE { NO ACTION | RESTRICT } ]
   [ ON DELETE { NO ACTION | RESTRICT | CASCADE | SET NULL } ]
 
Restrict    : Disallows the update or deletion of referenced data.
Set to null  : When referenced data is updated or deleted, all associated dependent data is set to NULL.
Set to default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
Cascade    : When referenced data is updated, all associated dependent data is correspondingly updated.
                When a referenced row is deleted, all associated dependent rows are deleted.
No action   : Disallows the update or deletion of referenced data.
 This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred.
(Oracle Database uses No Action as its default action.)

 

출처 : https://m.blog.naver.com/PostView.nhn?blogId=k65fac&logNo=220868098773&proxyReferer=https:%2F%2Fwww.google.com%2F