[SQL] DDL - Constraint (제약 조건)

SQL 에는 제약조건들이 몇 가지 있다.

• NOT NULL 제약 조건
• UNIQUE 제약 조건
• PRIMARY KEY 제약 조건 (NOT NULL + UNIQUE)
• FOREIGN KEY 제약 조건
• CHECK 제약 조건

---

NOT NULL 제약 조건

지정한 column에 NULL을 허용하지 않는 제약 조건 (NULL을 제외한 데이터의 중복은 허용됨)
column level 에서만 가능하다.

CREATE TABLE if NOT EXISTS user_notnull(
  user_no INT NOT NULL
);

이런 식은 가능하지만 아래는 불가능하다.

CREATE TABLE if NOT EXISTS user_notnull(
  user_no INT,
  NOT NULL(user_no)
)

user_notnull 이라는 테이블을 만들어본다.

DROP TABLE if EXISTS user_notnull;

CREATE TABLE if NOT EXISTS user_notnull(
  user_no INT NOT NULL,
  user_id VARCHAR(255) NOT NULL,
  user_pwd VARCHAR(255) NOT NULL,
  user_name VARCHAR(255) NOT NULL,
  gender VARCHAR(3),
  phone VARCHAR(255) NOT NULL,
  email VARCHAR(255)
) ENGINE=INNODB;

gender, email을 제외한 다른 column들은 전부 not null 조건이 들어간다.

---

INSERT
  INTO user_notnull
  (user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@gmail.com'),
(2, 'user02', 'pass02', '유관순', '여', '010-777-7777', 'yo77@gmail.com');

이런 식으로 넣는건 가능하다. -> gender, email을 제외한 다른 column에는 NULL인 값이 없으니까

INSERT
  INTO user_notnull
  (user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(3, 'user03', 'pass03', null, '남', '010-1234-5677', 'hong1234@gmail.com');

이렇게 user_name column에 null을 넣으니까 제약조건에 의해 에러가 생긴다.

---

UNIQUE 제약 조건

중복값이 들어가지 않도록 하는 제약 조건
UNIQUE 제약 조건은 중복되는 값은 허용하지 않지만 NULL 저장은 가능

• NOT NULL 제약조건과 다르게 column level 및 table level 모두 가능하다.

CREATE TABLE if NOT EXISTS user_unique(
  user_no INT NOT NULL UNIQUE,
  user_id VARCHAR(255) NOT NULL,
  user_pwd VARCHAR(255) NOT NULL,
  user_name VARCHAR(255) NOT NULL,
  gender VARCHAR(3),
  phone VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  UNIQUE(phone) -- phone에다가 컬럼댈 필요 없음
) ENGINE=INNODB;

INSERT
  INTO user_unique
  (user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@gmail.com'),
(2, 'user02', 'pass02', '유관순', '여', '010-777-7777', 'yo77@gmail.com');

INSERT
  INTO user_unique
  (user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(3, 'user03', 'pass03', '홍길동3', '남', '010-1234-5678', 'hong1234@gmail.com');

create -> insert -> insert 쿼리를 차례대로 실행하면 phone column이 중복해서 에러가 생긴다.

---

PRIMARY KEY 제약조건

not null + unique 제약 조건이라고 볼 수 있다.

모든 테이블은 반드시 primary key를 가져야 한다. (= 식별자를 가져야 한다.)
2개 이상의 제약 조건을 할 수는 없다.

---

CREATE TABLE if NOT EXISTS user_primarykey(
  user_no INT PRIMARY KEY,
  user_id VARCHAR(255) NOT NULL,
  user_pwd VARCHAR(255) NOT NULL,
  user_name VARCHAR(255) NOT NULL,
  gender VARCHAR(3),
  phone VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  UNIQUE(phone)
) ENGINE=INNODB;

이렇게 만든 뒤 확인해보면

DESC user_primarykey;

user_no : primary key, phone : unique 로 잘 보인다.

---

INSERT
  INTO user_primarykey
  (user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@gmail.com'),
(2, 'user02', 'pass02', '유관순', '여', '010-777-7777', 'yo77@gmail.com');

INSERT
  INTO user_primarykey
  (user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(1, 'user03', 'pass03', '홍길3동', '남', '010-1234-5673', 'hong1234@gmail.com');

잘 보면 user_no : 1, 2, 1 로 1이 겹치게 되면서 primary key (NOT NULL + UNIQUE) 에서 중복때문에 에러가 생긴다.

---

FOREIGN KEY 제약조건

외래 키(이하 FK)는 다른 테이블의 열을 참조하여 존재하는 값만 입력할 수 있는 제약 조건

멤버쉽을 생각해보자.
멤버십 등급 - 사용자 테이블이 하나씩 있다고 했을 때

'하나의 멤버쉽'은 '여러 사용자'에게 '하나'씩 배정이 된다. 즉, 멤버쉽 : 사용자 = 1 : N 관계인 것이다.

10점이 일반 고객, 20점이 우수 고객, 30점이 특별 고객 이라는 등급이 있을 때의 테이블은 이런 구조일 것이다.

CREATE TABLE if NOT EXISTS user_grade(
  grade_code INT PRIMARY KEY,
  grade_name VARCHAR(255) NOT NULL
);

INSERT
  INTO user_grade
VALUES
(10, '일반회원'),
(20, '우수회원'),
(30, '특별회원');

---

그럼 사용자 테이블은 FK 로 USER_GRADE의 PRIMARY KEY (이하 PK) 를 가지고 있어야 한다.

CREATE TABLE if NOT EXISTS user_foreignkey1 (
  user_no INT PRIMARY KEY,
  user_id VARCHAR(255) NOT NULL,
  user_pwd VARCHAR(255) NOT NULL,
  user_name VARCHAR(255) NOT NULL,
  gender VARCHAR(3),
  phone VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  grade_code INT,
  FOREIGN KEY(grade_code) REFERENCES user_grade(grade_code)
) ENGINE=INNODB;

이렇게 grade_code를 외래키로 가지면서 1:N 관계를 갖게 된다. 사용자 등록을 하는데 grade_code를 자세하게 보자.

• 잘 되는 경우

INSERT
  INTO user_foreignkey1
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-111-1111', 'hong@gmail.com', 10),
(2, 'user02', 'pass02', '홍길동전', '남', '010-222-2222', 'hong1@gmail.com', 20);

등급이 안에 속하는 10, 20 이므로 잘 된다.

• 잘 되는 경우 2 (NULL을 넣을 때)

INSERT
  INTO user_foreignkey1
VALUES
(3, 'user01', 'pass01', '홍길동', '남', '010-111-1111', 'hong@gmail.com', NULL);

이것 또한 된다. 왜??

외래키 중요한 부분

1. FK 값에 NULL을 넣으면 부모의 PK와는 관련이 없다는 의미로 사용할 수 있다.
즉, 연관관계의 주도권은 자식 테이블에게 있다.

2. FK 는 PK 와 UNIQUE KEY 2가지를 참조할 수 있다.

• 에러가 생길 때

INSERT
  INTO user_foreignkey1
VALUES
(4, 'user02', 'pass02', '홍길동전', '남', '010-222-2222', 'hong1@gmail.com', 40);

부모 테이블 안에 없는 점수를 넣으니까 에러가 발생한다.

---

삭제룰 적용

위 상황까지는 부모 - 자식 테이블이 연관된 상태이다.

근데 부모 테이블의 행을 삭제해야하는 상황이 오게 된다면
1. 행에 속하는 fk 를 가진 자식테이블을 삭제하고 지워야 하는 상황
2. 부모테이블의 행을 지우면 자식 테이블도 같이 지워지는 상황

이런 상황들이 있을 것이다.

해결방법으로는 1번에서 FK를 가진 자식테이블을 처음에 CREATE 할 때 삭제룰을 사용해 생성하는 방법이 있다.

위 부모 테이블, 자식 테이블들은 그대로 냅두고
삭제룰을 가진 제 2의 자식 테이블을 만들자.

CREATE TABLE if NOT EXISTS user_foreignkey2 (
  user_no INT PRIMARY KEY,
  user_id VARCHAR(255) NOT NULL,
  user_pwd VARCHAR(255) NOT NULL,
  user_name VARCHAR(255) NOT NULL,
  gender VARCHAR(3),
  phone VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  grade_code INT,
  FOREIGN KEY(grade_code) REFERENCES user_grade(grade_code)
  ON DELETE SET NULL
) ENGINE=INNODB;

foreign key에 붙여서 쓰는 ON DELETE SET NULL 같은 것을 삭제룰 이라고 한다.
set 뒤에 어떤 것이 오느냐에 따라 삭제룰은 바뀐다.

---

user_foreignkey2 에 새로운 값을 삽입해주고

INSERT
  INTO user_foreignkey2
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-111-1111', 'hong@gmail.com', 10);

삭제를 하려고 하니 문제가 생겼다.

user_foreighkey1 이 부모 테이블과 연결이 돼 있어 삭제를 할 수 없다.

---

그래서 truncate로 user_foreignkey1 을 초기화시켜주고 user_foreignkey2를 조회하면?

TRUNCATE user_foreignkey1;

SELECT * FROM user_foreignkey2;

---

삭제룰이 적용된 상태이므로 grade_code = 10인 부모테이블 user_grade를 삭제하면?

DELETE FROM user_grade WHERE grade_code = 10;

SELECT * FROM user_foreignkey2;

이렇게 삭제룰을 적용한 FOREIGN KEY 제약 조건을 마치겠다.

---

CHECK 제약 조건

CHECK 제약 조건은 "조건식을 활용한" 제약 조건이다.

gender, age에 CHECK 제약조건을 넣고 테이블을 만들어보자.

DROP TABLE if EXISTS user_check;

CREATE TABLE if NOT EXISTS user_check (
  user_no INT AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(255) NOT NULL,
  gender VARCHAR(3) CHECK(gender IN ('남', '여')), -- 제약조건을 활용한다
  age INT CHECK(age >= 19) -- 제약조건을 활용. 조건식에 따라 true, false 값
) ENGINE=INNODB;

INSERT
  INTO user_check
VALUES
(NULL, '홍길동', '남', 25),
(NULL, '이순신', '남', 33);

ENGINE=INNODB 를 써야하는 경우에 대해서는 나중에 알아보자.

위의 쿼리를 실행하고 조회해보면

SELECT * FROM user_check;

---

• 성별에 잘못된 값 넣어보기

INSERT
  INTO user_check
VALUES
(NULL, '아메바', '중', 19);

남, 여 가 아닌 다른 데이터를 넣으니 에러가 생겼다.

---

• 나이에 잘못된 값 넣어보기

INSERT
  INTO user_check
VALUES
(NULL, '유관순', '여', 16);

---

다음은 제약 조건이라기엔 애매할 수도 있는 사용자의 편리를 위한 DEFAULT 제약 조건에 대해 알아보자.

이번엔 tbl_country 라는 테이블을 만들어보았다.

DROP TABLE if EXISTS tbl_country;

CREATE TABLE if NOT EXISTS tbl_country (
  country_code INT AUTO_INCREMENT PRIMARY KEY,
  country_name VARCHAR(255) DEFAULT '한국',
  population VARCHAR(255) DEFAULT '0명',
  add_day DATE DEFAULT (CURRENT_DATE),
  add_time DATETIME DEFAULT (CURRENT_TIME)
) ENGINE=INNODB;

DEFAULT 는 알다시피 기본값과 같은 느낌이다.
DEFAULT 제약 조건을 입력해뒀다면 INSERT 문으로 데이터를 삽입할 때 DEFAULT를 사용하면 된다.

INSERT
  INTO tbl_country
VALUES
(NULL, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

여기까지 DDL에 대한 내용을 마치겠다.