무결성 제약 조건 - MySQL 코드 첨부

[ 무결성 제약 조건이란? ]

 무결성 제약 조건은 데이터의 정확성과 일관성을 유지하는데 도움을 주는 조건이다. 이를 통해서 데이터베이스에 저장되는 데이터는 무결성 제약 조건에 따라 특정 규칙을 따르도록 강제가 된다. 앞으로는 이에 해당하는 제약 조건의 종류와 해당 제약 조건을 MySQL에서 어떻게 기능을 지원하는지 알아보자.

[ 도메인 무결성 제약 조건 ]

 도메인 제약 조건은 특정 열에 대한 데이터 타입 범위 등을 정의한다. 예시에는 아래와 같은 종류가 있으며 해당 예시마다 코드도 남기도록 하겠다.

[ 값 범위 설정 ]

값 범위는 두 가지 예시가 있을 것 같다. 첫 번째로는 숫자형으로 된 값에서 특정 범위이내로 제한을 하는 경우, 두 번째로는 문자형 형태로 특정한 값('남', '여')만 받을 수 있는 경우가 있을 것이다.

 

[ 문자열 형태로 값 제한 ]

 해당 경우에는 ENUM을 사용하면 가능하다. 아래의 코드를 통해 테이블을 생성해보고 첫 번째, 두 번째 INSERT문을 실행하면 gender내에 허용된 값이기 때문에 가능하다. 하지만 'mal'의 경우 허용되지 않은 값이기에 해당 값은 거절당한다.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    gender ENUM('male', 'female')
);

INSERT INTO employees (id, name, gender) VALUES (1, 'John Doe', 'male');
INSERT INTO employees (id, name, gender) VALUES (2, 'Jane Smith', 'female');
INSERT INTO employees (id, name, gender) VALUES (3, 'Diana Lee', 'mal');

 

[ 숫자형에서 값 제한 ]

 해당 경우는 CHECK 제약조건을 통해서 사용이 가능하다. 지금 예시의 테이블에선 20-80살까지만 허용이 되고 이 외의 값은 거절당한다.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age BETWEEN 20 AND 80),
    gender ENUM('male', 'female')
);

 

[ 문자열 길이 설정 ]

 문자열 형태로 값을 제약함에서 사용한 코드를 그대로 가지고 왔다. 해당 코드 중에서 name에서 VARCHAR(100)이 있는데 해당 코드를 통해서 최대 100자까지 가변용량으로 name을 받는다는 걸 알 수 있다. 이 때 100자를 넘겨버리면 이 역시 데이터 베이스에서 에러 오류를 내서 보내준다.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    gender ENUM('male', 'female')
);

 

[ 디폴트 값 설정 ]

 디폴트 값 설정은 DEFAULT 키워드를 통해서 가능하며, 아무 값도 넣지 않는 NULL일 때 에러로 리젝을 내기보다는 30으로 임시 값을 넣어주는 용도로 사용한다. 예시는 좋지 않지만 회원 등급을 기본으로 설정하는 경우가 더 좋을 것 같다는 생각이 든다.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT DEFAULT 30 CHECK (age BETWEEN 20 AND 80),
    gender ENUM('male', 'female')
);

 

[ NOT NULL과 UNIQUE 제약 조건 ]

 NOT NULL은 해당 값이 들어올 때 NULL로 들어오면 안된다는 것을 의미하고 만약 NULL일 때는 에러를 내보낸다. 또한 UNIQUE 제약 조건은 같은 값이 없어야함을 의미하고 만약 내부에 있는 값이 들어오면 에러를 내보낸다. 이 경우 게임이면 닉네임 같은 경우가 있을 것이다. 

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    password VARCHAR(100) UNIQUE,
    age INT DEFAULT 30 CHECK (age BETWEEN 20 AND 80),
    gender ENUM('male', 'female')
);

[ 개체 무결성 제약 조건 ]

 기본키는 어떤 속성도 널 값을 가질 수 없고 하나의 값만 존재해야 한다. 그렇기 때문에 위에서 설명한 NOT NULL + UNIQUE를 합친 상태이다. 기본 키는 저장된 행 값을 식별하기 위한 데이터이기 때문에 이런 조건을 달아놓은 것 같다. 참고로 자동으로 기본 키 컬럼은 인덱스가 생성된다. 위에서 NOT NULL 및 UNIQUE와 함께 설명을 했으니 코드는 생략하겠다.

[ 참조 무결성 제약조건 ] 

 RDBMS에서는 외래키와 기본 키로 데이터를 조인해서 가져오고 양방향으로 서로를 가져올 수 있다. 이 때 부모 테이블과 자식 테이블 간의 관계를 유지하기 위해 참조 무결성 제약조건을 지켜주어야 한다. 

 

  아래의 경우는 Member와 Team 테이블이 생성된 상태이고 team_id를 통해 두 테이블의 연관관계를 표현하고 있다. 이 때 비정상 케이스의 값을 입력하면 외래키 제약 조건이라는 이유로 서버에서는 에러를 내보낸다. 어렵지 않지만 만약에 이미 들어가 있는데 부모가 사라진 경우 자식은 어떻게 해야될까? 자식은 외래키를 NULL로 가질 수 있을까? 같은 고민들을 해보아야 한다.

DROP TABLE IF EXISTS member;
DROP TABLE IF EXISTS Team;

CREATE TABLE Team (
    team_id INT PRIMARY KEY,
    team_name VARCHAR(100)
);

CREATE TABLE Member (
    member_id INT PRIMARY KEY,
    name VARCHAR(100),
    team_id INT,
    FOREIGN KEY (team_id) REFERENCES Team(team_id)
);


INSERT INTO Team (team_id, team_name) VALUES (1, "teamA");
# 정상 케이스
INSERT INTO Member (member_id, name, team_id) VALUES (1, "John", 1);
# 비정상 케이스
INSERT INTO Member (member_id, name, team_id) VALUES (1, "John", 2);

 

 우선 만약에 멤버가 아직 Team을 선택하지 않은 경우 Member는 등록해야 하는데 Team이 없다고 등록을 안할 수는 없다. 그렇기 때문에 NULL도 허용이 된다. 반드시 포함해야 하는 경우라면 NOT NULL 제약조건을 걸어버리자. 그러면 null일 때 에러를 내보낸다. 이 null 여부는 본인이 만드려고 하는 상황에 따라 자유롭게 선택이 가능하며 데이터에 대한 유연성과 일관성 및 무결성과의 trade off라고 생각하고 결정을 하면 된다.

# NOT NULL 적용전 team_id 없어도 가능!
INSERT INTO Member (member_id, name, team_id) VALUES (2, "Smith", null);

CREATE TABLE Member (
    member_id INT PRIMARY KEY,
    name VARCHAR(100),
    team_id INT NOT NULL,
    FOREIGN KEY (team_id) REFERENCES Team(team_id)
);

# NOT NULL 적용시 이제는 불가능!
INSERT INTO Member (member_id, name, team_id) VALUES (1, "Smith", null);

 

 그러면 Member가 Team이 설정이 되어있었는데, Team이 해체가 돼버려서 못 가져오는 경우가 있을 수 있다. 이 때는 어떻게 해야될까? 앞의 상황에서 Table의 값을 모두 지우고 다시 이 SQL문을 작동시켜보자. 그러면 safe update mode를 사용하기 때문에 안된다는 에러가 뜬다. 참고로 Team 테이블을 내리는 것도 이미 Member과의 참조 무결성 제약조건으로 인해서 불가능하다.

INSERT INTO Team (team_id, team_name) VALUES (1, "teamA");
INSERT INTO Member (member_id, name, team_id) VALUES (1, "John", 1);
# 참조 무결성 제약!
DELETE from Team where team_name = "teamA";
# 참조 무결성 제약
DROP TABLE IF EXISTS Team;

 

 결과부터 말하면 이 safe update mode를 해제할 생각을 하면 안된다. 안 그러면 없는 team_id를 가지고 있다가 나중에 어떤 장애가 일어날지 모른다. 그렇기 때문에, 삭제를 할 때 안정적으로 하기 위해서 도와주는 두 가지 추가 옵션이 있다. 

 

[ Cascade 옵션 ]

 Team이 삭제될 때 연관된 Member도 한꺼번에 다 가져와서 한번에 없애버리는 것이다. 예시가 Team이라서 그렇지 주문 같은 걸 예시로 하면 주문 시 배송 정보가 연관되어있다고 생각을 해보자. 이런 경우에는 주문시 배송정보를 DB에서 한번에 깔끔하게 지워버리는게 좋다. 예시 코드는 아래와 같다.

DROP TABLE IF EXISTS member;
DROP TABLE IF EXISTS Team;

CREATE TABLE Team (
    team_id INT PRIMARY KEY,
    team_name VARCHAR(100)
);

CREATE TABLE Member (
    member_id INT PRIMARY KEY,
    name VARCHAR(100),
    team_id INT NOT NULL,
    FOREIGN KEY (team_id) REFERENCES Team(team_id) on DELETE cascade
);

INSERT INTO Team (team_id, team_name) VALUES (1, "teamA");
INSERT INTO Member (member_id, name, team_id) VALUES (1, "John", 1);
DELETE from Team where team_name = "teamA";

# TeamA만 삭제했는데 John도 같이 삭제됨!
SELECT * FROM Team;
SELECT * FROM Member;

 

 [ SET NULL 옵션 ]

 유저가 특정 동아리나 카페에 가입을 했는데 동아리나 카페가 사라졌다고 유저 정보를 날려버리는 건 절대 안된다. 그런 상황의 경우에는 DB에서 Member에서 연관관계 정보를 싹다 지워버리는게 더 좋은 선택이다. 그런 상황에서는 SET NULL을 사용하면 된다.

DROP TABLE IF EXISTS Member;
DROP TABLE IF EXISTS Team;

CREATE TABLE Team (
    team_id INT PRIMARY KEY,
    team_name VARCHAR(100)
);

CREATE TABLE Member (
    member_id INT PRIMARY KEY,
    name VARCHAR(100),
    team_id INT,
    FOREIGN KEY (team_id) REFERENCES Team(team_id) on DELETE SET NULL
);

INSERT INTO Team (team_id, team_name) VALUES (1, "teamA");
INSERT INTO Member (member_id, name, team_id) VALUES (1, "John", 1);
DELETE from Team where team_name = "teamA";

# TeamA만 삭제했는데 John의 Team이 null이 됨!
SELECT * FROM Member;

 

[ 참고 자료 ]

https://inpa.tistory.com/entry/DB-%F0%9F%93%9A-%EB%AC%B4%EA%B2%B0%EC%84%B1-%EC%A0%9C%EC%95%BD-%EC%A1%B0%EA%B1%B4-%F0%9F%95%B5%EF%B8%8F-%EC%A0%95%EB%A6%AC

https://shuu.tistory.com/68