[DATABASE] 참조무결성과 CASCADE 그리고 외래키(Foreign Key)그리고 옵션 지정방법에 대해서 알아보자

참조 무결성이란 데이터베이스 상의 참조가 모두 유요함을 일컫는다. 관계형 데이터베이스에서 하나의 속성이 다른 테이블의 속성을 참조하고 있다면, 참조한 해당 속성이 필히 존재해야한다.

 

참조 무결성을 통해 Primary Key(PK, 기본키)와 Foreign Key(FK, 외래키) 간의 관계가 항상 유효하도록 관리한다.

따라서 PK를 참조하는 FK가 있다면, 해당 PK는 수정과 삭제가 불가능해진다.

 

FK의 역할은 두 개의 테이블을 연결해주는 연결 다리 역할을 한다. 기본키가 중복된 데이터가 하나의 테이블에 삽입되는 것을 방지하는 역할을 하는것처럼 외래키 역시 비슷하게 문제를 방지하는 역할을 수행한다.(무결성 유지를 위해서)

FK는 새롭게 추가되는 행에서 외래키에 해당하는 값이 외래키가 참조하는 테이블에 존재하는지를 체크한다. 그렇기 때문에 FK는 다른 테이블의 PK필드를 참조하거나 유일해야만 한다.

 

 

---

 

 

SQL문으로 테스트를 진행해보자.

 

1) TEAM table의 id값을 참조하는 PLAYER테이블을 생성했다.

 

 

2) 해당 테이블들에 값을 넣어준다.

 

 

3) 넣어준 후 테이블을 확인하면 밑에처럼 출력된다. 

TEAM table의 id 값과 PLAYER table의 team_id값이 같다는것이 확인이 된다.

 

 

4) TEAM table을 참조하는 테이블이 생겼을 경우

UPDATE 를 진행하면 아래와 같은 에러가 발생한다.

DELETE 를 진행하면 아래와 같은 에러가 발생한다.

 

 

5) 그렇다면 어떻게 지우거나 변경을 할 수 있을까?? 이럴때 사용하는 것이 CASCADE이다.

* UPDATE 를 위해서는 [ ON UPDATE CASCADE ]를 붙여주면 된다. 그렇다면 밑에처럼 변경이 가능해진다.

 

TEAM table의 id와 PLAYER table의 team_id가 1 > 2로 변경된것을 확인할 수 있다.

 

 

* DELETE 를 위해서는 2가지 방법을 사용할 수 있다.

ON DELETE CASCADE 사용시 TEAM(부모)테이블의 값이 삭제되면서 참조(자식)되는 테이블의 값도 삭제시킨다.

ON DELETE SET NULL 사용시 TEAM(부모) 테이블의 값은 삭제되지만 참조(PLAYER)하는 테이블의 값은 NULL 로 변경이 된다.

 

 

 

 

결론적으로, 어떤 테이블안에 값을 변경하거나 지워야되는 데이터 정의어(Data Definition Language)를 사용하기 위해서는 미리 CASCADE 명령어를 붙여놓아야 할 것이다. 물론, 무조건적으로 변경을 열어놓거나 삭제를 열어놓는다면 대 참사가 생길것이니, 자신의 프로젝트안에서 맞춰서 설정해야 할 것이다.

 

 

---

CACADE default 값은 무엇일까?

 

우리가 [ ON UPDATE CASCADE 또는 ON DELETE SETNULL 또는 ON DELETE CASCADE ] 를 선언하지 않는다면 

 

그것은 [ ON DELETE RESTRICT(삭제시 제약) 과 ON UPDATE RESTRICT(갱신시 제약) ] 이다.

위의 예시들처럼, 제약사항을 변경해주지 않는다면 데이터의 정합성을 위해 수정이나 삭제가 불가능한 것이다.

 

FK를 지정 시 옵션 지정은 4가지가 가능하다.

1. RESTRICT(기본값) : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 변경/삭제가 취소된다.(제한)

 

2. CASCADE : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 함께 변경/삭제 된다.

 

3. NO ACTION : MYSQL에서는 RESTRICT와 동일하다.

 

4. SET NULL : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 참조하고 있는 값은 NULL로 세팅한다.

---

 

 

글 읽을 때 도움되는 부가정보

 

관계형 데이터베이스(Relational Database, RDB)

관계형 데이터베이스는 키(Key)와 값(value)들의 간단한 관계를 테이블화 시킨 매우 간단한 원칙의 전상정보 데이터 베이스이다.

 

관계형 모델은 데이터의 컬럼(column)과 로우(row)를 이루는 하나 이상의 테이블(또는 관계)로 정리하며, 고유 키(Primary key)가 각 로우를 식별한다. 로우는 레코드나 튜플로 부른다. 일반적으로 각 테이블/관계는 하나의 엔티티 타입(고객이나 제품과 같은)을 대표한다. 로우는 그 엔티티 종류의 인스턴스를 대표하며 컬럼은 그 인스턴스의 속성이 되는 값들을 대표한다.

 

키(Key)는 테이블의 각 로우에는 저만의 고유 키(key)가 있다. 한 테이블 안의 로우는 다른 테이블들의 로우로 연결이 가능한데, 이는 연결된 로우의 고유 키를 위한 위한 컬럼을 추가함으로써 이루어진다. (이러한 컬럼들은 외래 키로 부른다)

 

관계(Relationships)는 테이블 간에 둘 다 존재한다. 이 관계들은 일대일, 일대다, 다대다, 이렇게 세 가지 형태로 이루어진다. 대부분의 관계형 데이터베이스들은 각 로우의 각 컬럼이 하나의 값만을 보유할 수 있도록 설계되어 있다. (값은 원자적이다)

 

 

 

---

 

 

 

referance

* 관계형 데이터베이스 - 위키백과

* 참조 무결성과 Cascade 옵션

* [DB] DDL, DML, DCL 이란?

* 19. 외래키 이해하기

* 외래키 - 해시넷

* 외래 키(foreign key) 란

* 외래 키 - 위키백과

* Foreign Key의 Cascade 옵션

* MYSQL 외래키(Foreign key) 지정(RESTRICT, CASCADE, NO ACTION, SET NULL)