[MySQL] 데이터베이스 모델링 | 참조 무결성

 

---

 

 

1. 참조 무결성

 

1) 참조 관계

 

👩🏻‍🚀  제 1 정규화에서 예로 들었던 직원과 프로젝트 테이블을 생성하고 관계를 맺는다

       ➡️ 두 개의 테이블을 만들되 프로젝트에 비용 필드를 추가

 

 

📌 먼저 부모 테이블에 해당하는 직원 테이블을 생성하고 샘플 데이터를 입력

 

CREATE TABLE `tEmployee`
(
`name` CHAR(10) PRIMARY KEY, // 직원의 이름을 기본키로 지정
`salary` INT NOT NULL, // 월급
`addr` VARCHAR(30) NOT NULL // 주소
);
INSERT INTO `tEmployee` VALUES ('김상형', 650, '이천시');
INSERT INTO `tEmployee` VALUES ('문종민', 480, '대구시');
INSERT INTO `tEmployee` VALUES ('권성직', 625, '안동시');

 

 

📌 다음은 직원 테이블과 관계를 맺을 프로젝트 테이블을 생성

 

CREATE TABLE `tProject`
(
`projectID` INT PRIMARY KEY,  --기본키인 projectID는 자동 증가열로 정의하는 것이 간편
`employee` CHAR(10) NOT NULL, --담당자
`project` VARCHAR(30) NOT NULL, --프로젝트이름 
`cost` INT --비용
);
INSERT INTO `tProject` VALUES (1, '김상형', '홍콩 수출건', 800);
INSERT INTO `tProject` VALUES (2, '김상형', 'TV 광고건', 3400);
INSERT INTO `tProject` VALUES (3, '김상형', '매출분석건', 200);
INSERT INTO `tProject` VALUES (4, '문종민', '경영 혁신안 작성', 120);
INSERT INTO `tProject` VALUES (5, '문종민', '대리점 계획', 85);
INSERT INTO `tProject` VALUES (6, '권성직', '노조 협상건', 24);

 

 

 

👾  employee 외래키는 직원 테이블의 name키를 가리키며 이 관계를 통해 프로젝트의 담당자가 누구인지 알 수 있음

    💡 두 테이블은 담당한다는 관계 = (참조)를 맺음

 

 

* 참고 *

  -  테이블 이름은 표현하는 엔티티로 붙이는 것이 좋음

     ➡️ 도시의 정보를 다루니 tCity로 이름을 붙이고 직원 목록은 tStaff으로 붙임 프로젝트명은 tProject가 어울림.

  -  레코드 여러 개를 저장한다고 해서 굳이 복수형으로 작성할 필요는 없음

     ➡️ tCities, tStaffs, tProjects로 해도 문제는 없지만 테이블에는 어차피 여러 개의 레코드가 들어가니 복수형일 필요 없음

 

 

---

 

2) 외래키 제약

 

👩🏻‍💻 직원과 프로젝트의 샘플 데이터는 규칙에 맞게 입력하여 참조 관계가 정확

 

 

📌 새로 레코드를 삽입할 때는 아직 문제가 있음

 

INSERT INTO `tProject` VALUES (7, '홍길동', '원자재 매입', 900);

 

  👾 새로운 프로젝트의 담당자로 지정한 '홍길동'은 직원 테이블에 존재하지 않음

  👾 외래키가 존재하지 않는 잘못된 키를 가리키고 있지만 이상 없이 잘 삽입됨

 

 

📌 다음 명령도 문제가 있음

 

DELETE FROM `tEmployee` WHERE name = '김상형';

 

  👾 이 명령은 '김상형' 직원을 삭제

        ➡️ 이 직원이 맡고 있는 모든 프로젝트는 담당자를 잃어버림.

  👾 외래키가 가리키는 대상이 사라져 참조 무결성이 깨져 버림

  👾 관계를 기반으로 동작하는 RDB의 참조 무결성이 깨지면 이후의 동작을 보증할 수 없음

 

 

👩🏻‍💻 DB 엔진은 시키는대로 할 뿐 실행을 거부할 이유 x  ➡️  이런 이유로 외래키(FK) 제약이 필요

👩🏻‍💻 외래키 제약은 어떤 필드가 외래키인지, 어떤 테이블의 무슨 키를 참조하는지 지정

👩🏻‍💻 외래키 제약이 있으면 참조 무결성이 깨지는 명령의 실행을 거부

 

 

📌 직원 테이블은 원상 복구해 두고 tProject 테이블은 다시 정의

 

CREATE TABLE `tProject` (
`projectID` INT PRIMARY KEY,
`employee` CHAR(10) NOT NULL,
`project` VARCHAR(30) NOT NULL,
`cost` INT,
 CONSTRAINT FK_emp FOREIGN KEY(`employee`) REFERENCES `tEmployee`(name`)
 // 마지막 필드 끝에 콤마를 찍고 제일 아래에 외래키 제약을 추가
);

 

 

  👾  제약의 이름과 종류를 지정하고 REFERENCES 키워드 다음에 테이블(필드) 형식으로 참조하는 테이블과 키를 지정

  👾  오라클과 SQL Server는 외래키 필드 선언문에 컬럼 제약으로 지정할 수도 있음

 

`employee` ...CHAR(10) NOT NULL REFERENCES
`tEmployee`(name),

 

  👾 외래키 제약은 employee 필드가 tEmployee 테이블의 name 필드와 연결되었음을 선언

        ➡️ 이후 DBMS는 이 제약을 위반하는 모든 명령의 실행을 거부

 

INSERT INTO `tProject` VALUES (7, '홍길동', '원자재 매입', 900);
// ORA-02291: 무결성 제약조건(SYSTEM.FK_EMP)이 위배되었습니다- 부모 키가 없습니다

 

DELETE FROM `tEmployee` WHERE `name` = '김상형';
// ORA-02292: 무결성 제약조건(SYSTEM.FK_EMP)이 위배되었습니다- 자식 레코드가 발견되었습니다

 

  💡 둘 다 에러 메시지를 출력하고 실행을 거부

         ➡️ 원하는대로 하려면 다음 두 명령을 순서대로 실행

 

INSERT INTO `tEmployee` VALUES ('홍길동', 330, '장성');
INSERT INTO `tProject` VALUES (7, '홍길동', '원자재 매입', 900);

 

  💡 직원을 먼저 등록해야 이 직원에게 프로젝트를 맡길 수 있음

        ➡️ 마찬가지로 직원을 삭제하려면 다음 두 명령을 순서대로 실행

DELETE FROM `tProject` WHERE `employee` = '김상형';
DELETE FROM `tEmployee` WHERE `name` = '김상형';

 

  💡 직원이 맡고 있는 프로제트를 모두 제거하거나 아니면 다른 직원에게 넘겨야 이 직원을 컷할 수 있음

 

 

 

  📌  FK 제약이 설정되어 있는 상태에서 다음 쿼리문도 에러 처리

DROP TABLE `tEmployee`;

 

  👾 직원 테이블을 삭제하면 프로젝트 테이블 전체가 무효해짐

         ➡️ 정직원 테이블을 삭제하려면 프로젝트 테이블부터 비워야 함

 

 

👩🏻‍💻 DBMS가 외래키 제약에 대한 규칙을 정확하게 알고 있으므로 무결성을 해치는 모든 명령을 원천적으로 차단

👩🏻‍💻 외래키 제약은 참조 무결성을 지키는 강력한 수단

 

 

---

 

3) 연계 참조 무결성 제약

 

 

👩🏻‍🚀 외래키 제약관련 작업을 자동화하여 한 번에 처리하는 연계 참조 무결성 제약

👩🏻‍🚀 관련키의 삭제나 수정을 무조건 금지하는 것이 아니라 추가 동작까지 자동으로 처리하여 무결성을 유지하는 기능

👩🏻‍🚀 부모 테이블을 변경하면 자식 테이블까지 알아서 수정

 

  💡 이 기능을 사용하려면 외래키 제약 뒤에 다음 선언을 추가

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

 

👾 ON DELETE는 참조되는 키가 삭제될 때의 동작을 정의하며 ON UPDATE는 참조되는 키가 변경될 때의 동작을 정의

 

  📌  각각에 대해 4개의 옵션을 지정할 수 있음

      * NO ACTION은 아무것도 하지 않고 실패하도록 내버려 두는 것이며 이 옵션이 디폴트

      * CASCADE는 참조되는 키와 연결되어 있는 외래키를 자동으로 삭제하거나 변경

      * SET NULL이나 SET DEFAULT는 외래키를 NULL이나 기본값으로 변경하는데 외래키가 NULL 허용이거나 기본값이 지정되어 있어야 함

 

 

  📌 tProject 테이블을 삭제한 후 다음 명령으로 다시 생성

 

CREATE TABLE `tProject` (
`projectID` INT PRIMARY KEY,
`employee` CHAR(10) NOT NULL,
`project` VARCHAR(30) NOT NULL,
`cost` INT,

CONSTRAINT FK_emp FOREIGN KEY(`employee`) REFERENCES `tEmployee`
ON DELETE CASCADE
);

 

 

👾 ON DELETE에 대해 CASCADE 옵션을 지정하여 필요한 추가 처리를 자동으로 하도록 함

👾 이 상태에서 다음 명령으로 직원을 삭제

 

DELETE FROM `tEmployee` WHERE `name` = '김상형';

 

  💡 외래키 제약만 걸려 있다면 이 명령은 당연히 에러지만 CASCADE 옵션이 지정되어 있어 에러 없이 실행 가능

 

 

  📌 실행하기 전 후 차이

 

 

 

 

 

👾 직원 뿐만 아니라 tProject에 이 직원이 담당한 프로젝트도 같이 삭제

👾 연계 참조란 이런 식으로 하나를 삭제하면 연결된 다른 테이블을 자동으로 수정하는 기능

 

  💡 만약 연계에 의해 삭제되는 레코드에 또 다른 연계 참조 무결성 제약이 걸려 있다면 연쇄적으로 수정

 

 

---

 

 

👾 ON UPDATE 선언을 하면 참조키 수정시 관련 외래키를 같이 수정하며 동작 방식은 ON DELETE와 유사

👾 SQL Server와 MariaDB는 이 기능을 지원하지만 오라클은 ON DELETE까지만 지원하며 똑같은 기능을 구현하려면 트리거를 사용해야 함

 

 

  📌  tProject 테이블을 삭제한 후 다음 명령으로 다시 생성

  📌  ON DELETE와 ON UPDATE에 대해 모두 CASCADE 동작을 지정

 

CREATE TABLE `tProject`(
`projectID` INT PRIMARY KEY,
`employee` CHAR(10) NOT NULL,
`project` VARCHAR(30) NOT NULL,
`cost` INT,
CONSTRAINT FK_emp FOREIGN KEY(`employee`) REFERENCES `tEmployee`
ON DELETE CASCADE ON UPDATE CASCADE
);

 

👾  직원을 삭제하면 연관 정보도 같이 삭제

 

 

  📌  이번에는 직원이 이름을 변경한 상황을 가정하여 UPDATE 명령으로 참조키를 수정

 `tEmployee` SET `name` = '문사장' WHERE `name` = '문종민';

 

👾 이름이 바뀌는 것은 원래의 이름이 없어지는 것과 같아 프로젝트의 담당자가 무효해짐

👾 외래키 제약만 걸려 있다면 담당자의 이름만 바꾸는 것은 불가능

👾 연계 참조 무결성 제약이 걸려 있으면 참조되는 키를 수정할 수 있음

      ➡️ 직원 이름을 수정하면 tProject의 외래키도 같이 변경

      ➡️ 담당자의 이름과 프로젝트의 담당자가 동시에 바뀌므로 무결성은 여전히 유지

 

 

💡 자동으로 처리한다는 면에서 편리하지만 이 기능은 함부로 사용 x, 실무에서도 잘 사용하지 않음

 

 

 

 

 

[ 내용 참조 : IT 학원 강의 ]