DDL/CONSTRAINT) FOREIGN KEY 제약 조건 (FK 제약 조건 삭제 옵션, 식별 관계/비식별 관계)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

CONSTRAINT

- 제약 조건

- 테이블의 컬럼에 대해서 데이터 무결성을 지키기 위해 설정하는 조건

※ 데이터 무결성이란?

데이터가 일관되고, 정확하게 유지 되는 것

- not null을 제외하고 constraint_name을 반드시 지정 및 table_level로 작성

 

① not null (C) : 해당 컬럼에 null 값을 허용하지 않음

② unique (U) : 레코드 별로 해당 컬럼에 중복을 허용하지 않음

③ primary key (P) : 식별자 컬럼을 지정, 레코드를 구별하기 위한 고유 값을 가진 컬럼 → 테이블 당 하나만 허용 / null 허용 X

④ foreign key (R) : 외래키. 부모 테이블의 특정 컬럼 값만 사용 가능하도록 제약

⑤ check (C) : 해당 컬럼의 값의 범위/목록을 제한 (도메인을 설정하는 제약 조건)

 

---

FORIEGN KEY

- 외래키

- 참조무결성을 위한 제약 조건

- 부모 테이블 (참조되는 테이블)의 존재하는 값만 자식 테이블 (참조하는 테이블. 외래키)에서 사용 가능하도록 제한

- 부모 테이블의 참조 컬럼은 PK/UQ 제약 조건이 걸려 있어야 함 (중복 값이 있는 컬럼을 참조 할 수 없음)

 

부모 테이블

-- 부모 테이블
create table shop_member(
    id varchar2(20),
    name varchar2(50) not null,
    constraint pk_shop_member_id primary key(id)
);

insert into shop_member values('honggd', '홍길동');
insert into shop_member values('sinsa', '신사임당');
insert into shop_member values('sejong', '세종대왕');

select * from shop_member;

 

자식 테이블 - 외래키 설정

-- 자식 테이블
create table shop_buy(
    buy_no number,
    member_id varchar2(20),
    product_id varchar2(20),
    cnt number default 1,
    buy_date date default sysdate,
    constraint pk_shop_buy_no primary key(buy_no),
    constraint fk_shop_buy_member_id foreign key(member_id) references shop_member(id)
);

insert into 
    shop_buy(buy_no, member_id, product_id)
values (1, 'honggd', '축구화001');

insert into
    shop_buy(buy_no, member_id, product_id)
values (2, 'sinsa', '볼링화123');

insert into
    shop_buy(buy_no, member_id, product_id)
values (3, 'chany', '등산화123');
-- ORA-02291: integrity constraint (KH.FK_SHOP_BUY_MEMBER_ID) violated - parent key not found

PK 제약조건이 걸려있는 부모 테이블의 ID를 참조하고 있는 자식 테이블의 member_id가 FK 제약 조건이 설정되어 있습니다.

따라서 shop_buy(자식 테이블)의 member_id에 값은 shop_member(부모 테이블)의 id에 존재하는 값이여야 합니다.

 

현재 shop_member에 id에는 'honggd', 'sinsa', 'sejong'이 들어가 있기 때문에 shop_buy에서 'chany'를 삽입하려고 하니,

"ORA-02291: integrity constraint (KH.FK_SHOP_BUY_MEMBER_ID) violated - parent key not found" 오류 메세지가 확인 되었습니다.

 

FK로 지정 되어 있는 컬럼에 null 값을 넣으면 어떻게 될까?

insert into
    shop_buy(buy_no, member_id, product_id)
values (3, null, '등산화123');

select * from shop_buy;

FK컬럼의 역할은 위의 내용이 전부.. 따라서 FK 컬럼에 null 값은 허용 되어 추가 된 것을 확인할 수 있습니다.

null 값을 허용하고 싶지 않다면, not null 제약 조건을 설정해주면 됩니다.

---

FK 제약조건 삭제 옵션

① on delete restricted (기본값) : 부모 테이블 먼저 삭제 불가

② on delete set null : 부모 레코드 삭제 시 자식 레코드의 FK 컬럼을 NULL로 변환 처리

③ on delete cascade : 부모 레코드 삭제 시 자식 레코드도 삭제

 

☞ on delete restricted (기본값)

- 부모 테이블 먼저 삭제 불가

-- on delete restricted
-- 부모 테이블의 레코드 삭제
delete from
    shop_member
where
    id = 'honggd';  
-- ORA-02292: integrity constraint (KH.FK_SHOP_BUY_MEMBER_ID) violated - child record found

-- drop도 불가!
-- drop table shop_member;  
-- ORA-02449: unique/primary keys in table referenced by foreign keys

기존 shop_member 테이블 생성 시 삭제 옵션을 따로 지정 안했으므로 기본 값인 on delete restricted로 설정 되어있습니다.

 

"ORA-02292: integrity constraint (KH.FK_SHOP_BUY_MEMBER_ID) violated - child record found"

부모 레코드를 삭제하려고 하니 'honggd' 행을 참조하는 자식 레코드가 발견 되어서 삭제가 불가하다는 에러메세지가 출력 되었습니다.

delete가 불가하니 테이블 자체를 drop하는 것도 불가하겠죠.

 

자식 테이블의 레코드를 먼저 삭제한 후 부모 테이블의 레코드를 삭제 해야 합니다.

-- 자식 레코드 먼저 삭제 -> 부모 레코드 삭제
delete from
    shop_buy
where
    member_id = 'honggd';

-- 부모 테이블의 레코드 삭제
delete from
    shop_member
where
    id = 'honggd';
    
select * from shop_member;

 

☞ on delete set null

- 부모 레코드 삭제 시 자식 레코드의 FK컬럼을 NULL로 변경

-- on delete set null
-- drop table shop_buy;
create table shop_buy(
    buy_no number,
    member_id varchar2(20),
    product_id varchar2(20),
    cnt number default 1,
    buy_date date default sysdate,
    constraint pk_shop_buy_no primary key(buy_no),
    constraint fk_shop_buy_member_id foreign key(member_id) references shop_member(id) on delete set null
);

insert into 
    shop_buy(buy_no, member_id, product_id)
values (2, 'sinsa', '볼링화123');

select * from shop_buy;

 

-- 부모 레코드 삭제
delete from
    shop_member
where
    id = 'sinsa';   -- 자식 레코드의 FK컬럼을 NULL로 변경

select * from shop_member;
select * from shop_buy;

shop_member

shop_buy

부모 레코드를 삭제하니, 자식 레코드의 FK컬럼 (member_id)이 null로 변경 된 것을 확인할 수 있습니다.

 

☞ on delete cascade

- 부모 레코드 삭제 시 자식 레코드도 삭제

-- on delete cascade
-- drop table shop_buy;
create table shop_buy(
    buy_no number,
    member_id varchar2(20),
    product_id varchar2(20),
    cnt number default 1,
    buy_date date default sysdate,
    constraint pk_shop_buy_no primary key(buy_no),
    constraint fk_shop_buy_member_id foreign key(member_id) references shop_member(id) on delete cascade
);

insert into
    shop_buy(buy_no, member_id, product_id)
values (1, 'sejong', '축구화001');

select * from shop_buy;

 

-- 부모 레코드 삭제
delete from
    shop_member
where
    id = 'sejong';
    
select * from shop_member;
select * from shop_buy;

부모 레코드를 삭제하니, 자식 레코드의 FK컬럼도 같이 삭제 된 것을 확인할 수 있습니다.

 

---

식별관계/비식별관계

① 식별 관계 : FK컬럼을 다시 PK컬럼으로 지정 → 부모/자식 테이블 간의 1:1 관계

② 비식별 관계 : FK컬럼을 PK컬럼(단독)으로 사용하지 않음 → 부모/자식 테이블 간의 1:N 관계

create table person_2(
    id varchar2(20),
    name varchar2(50),
    constraint pk_person_2_id primary key(id)
);

insert into person_2 values ('yoogs', '유관순');

 

☞ 식별 관계

- PK(기본키)에 FK(외래키)가 포함

-- 식별 관계
create table person_address_2(
    person_id varchar2(20),
    addr varchar2(200) not null,
    constraint fk_person_address_2_id foreign key(person_id) references person_2(id),
    constraint pk_person_address_2_id primary key(person_id)
);

insert into
    person_address_2
values ('yoogs', '서울시 강남구 역삼동');

insert into
    person_address_2
values ('yoogs', '서울시 강동구 천호동');
-- ORA-00001: unique constraint (KH.PK_PERSON_ADDRESS_2_ID) violated

person_id는 부모 테이블(person_2)의 id를 참조하고 있는 외래키이면서 person_address_2 테이블의 PK로 지정 되어있습니다.

따라서 'yoogs'에 대한 데이터는 addr이 달라도 유일해야 한다는 것을 확인할 수 있습니다.

 

'yoogs', '서울시 강동구 천호동'을 추가하려고 하니,

"ORA-00001: unique constraint (KH.PK_PERSON_ADDRESS_2_ID) violated" PK키로 지정 되어 있으므로 중복 값이 들어 갈 수 없다는 의미의 에러메세지가 출력 되는 것이죠.

 

☞ 비식별관계

- PK(기본키)에 FK(외래키)가 포함 되지 않고, 외부 테이블을 참조만 하는 관계

-- 비식별관계
-- drop table person_address_2;
create table person_address_2(
    person_id varchar2(20),
    addr varchar2(200) not null,
    constraint fk_person_address_2_id foreign key(person_id) references person_2(id)
);

insert into
    person_address_2
values ('yoogs', '서울시 강남구 역삼동');

insert into
    person_address_2
values ('yoogs', '서울시 강동구 천호동');

기본키에 외래키가 포함 되지 않았으므로, 비식별관계입니다. (기본키 자체가 설정 되어있지 않음)

 

-- drop table person_address_2;
create table person_address_2(
    person_id varchar2(20),
    addr varchar2(200) not null,
    constraint fk_person_address_2_id foreign key(person_id) references person_2(id),
    constraint pk_person_address_2_id_addr primary key(person_id, addr)
);

insert into
    person_address_2
values ('yoogs', '서울시 강남구 역삼동');

insert into
    person_address_2
values ('yoogs', '서울시 강동구 천호동');

select * from person_address_2;

언뜻 보면, 기본키에 외래키가 포함 되어 식별 관계인 것처럼 보이지만 복합컬럼PK로 설정하였기 때문에 비식별관계에 포함됩니다.

외래키를 단독적인 기본키로 사용하지 않았기 때문이죠.

primary key(person_id)와 primary key(person_id, addr)는 엄연히 다른 것이기 때문!!