[DB] 테이블 제약조건

1. CONSTRAINTS(제약조건)

💡 CONSTRAINTS(제약조건) 이란?
: 제약조건으로 테이블에 데이터가 입력되거나 수정될 경우에 발생하는 규칙
: 데이터 무결성 보장

• 테이블 선언시 Column-Level 과 Table-Level 에 선언가능
  
  
  

1-1. NOT NULL

💡 NULL 값을 허용하지 않는 제약조건

1) NOTNULL 조건을 포함한 테이블 생성

<코드>

-- 테이블 생성 (NOT NULL 제약조건 포함)
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;

-- 조회
DESC user_notnull;

<실행결과>

• 테이블 생성시 NOT NULL 제약 조건을 선언 가능
• 아무것도 선언되지 않는 행을 NULLABLE(NULL가능) 을 의미
  
  

2) 생성된 테이블에 NULL값 넣어보기

<코드>

-- NOTNULL 제약조건을 가진 행에 NULL값 대입
INSERT
  INTO user_notnull
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
( 3, 'user03', 'pass03', NULL , '남', '010-1234-5678', 'hong123@gmail.com');

<실행결과>

• NOTNULL 제약조건을 가진 'USER_NAME' 컬럼에 NULL값을 INSERT하니 오류 발생
  
  
  

1-2. UNIQUE

💡 중복값 을 허용하지 않는 제약조건

1) UNIQUE 조건을 포함한 테이블 생성

<코드>

-- 테이블 생성(UNIQUE 제약조건 포함) 
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)
) ENGINE = INNODB;

-- 조회
DESC user_unique;

<실행결과>

• 테이블 생성시 UNIQUE 제약 조건을 선언 가능
• PRIMARY KEY는 NOT NULL + UNIQUE의 기능을 가짐
  
  

2) 생성된 테이블에 중복값 넣어보기

<코드>

-- PHONE 컬럼에 중복값을 대입
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', 'yu77@gmail.com'),
( 3, 'user03', 'pass01', '홍길동2' , '남', '010-1234-5678', 'hong123@gmail.com');

<실행결과>

• UNIQUE 제약 조건을 포함한 'PHONE' 컬럼에 중복 값을 INSERT하니 오류 발생
  
  
  

1-3. PRIMARY KEY(기본 키)

💡 특정 레코드를 유일하게 식별(식별자) 하기 위해 사용되는 컬럼

💡 PRIMARY KEY 특성
1. 테이블에서 한 행의 정보를 찾기 위해 사용할 컬럼
2. 테이블에 대한 식별자 역할
3. PK = NOT NULL + UNIQUE 제약조건
4. 테이블 1개당 1개만 설정 (1개는 필수)
5. Column-Level/ Table-Level 둘다 설정 가능
6. 한 개 컬럼에 설정 가능 / 여러 개 컬럼에 묶어서 설정 가능(복합키: Table-Level만 설정)

1) PRIMARY KEY 조건을 포함한 테이블 생성

<코드>

--  테이블 생성 (PRIMARY KEY 포함)
CREATE TABLE if NOT EXISTS user_primarykey(
--  user_no INT PRIMARY KEY ,
  user_no INT,
  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),
  PRIMARY KEY(user_no)
) ENGINE = INNODB;

-- 조회
DESC user_primarykey;

<실행결과>

• 테이블 생성시 PRIMARY KEY 제약 조건을 Table-Level & Column-Level 에서 모두 설정 가능

2) 생성된 테이블에 NULL값 넣어보기

<코드>

-- PK에 해당하는 'user_no' 컬럼에 NULL값 대입
INSERT 
  INTO user_primarykey
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(null, 'user03', 'pass03', '이순신', '남', '010-777-7777', 'lee222@gmail.com');

<실행결과>

• PRIMARY KEY 는 NOT NULL + UNIQUE 제약 조건을 모두 포함하므로, NULL값을 INSERT하면 오류 발생(AUTO_INCREMENT 제약조건을 추가하면 NULL값 가능)
  
  

3) 생성된 테이블에 중복값 넣어보기

<코드>

-- PK에 해당하는 'user_no' 컬럼에 중복값 대입
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', 'yu77@gmail.com'),
(1, 'user012', 'pass012', '홍길동2', '남2', '010-1234-5678', 'hong1232@gmail.com');

<실행결과>

• PRIMARY KEY 는 NOT NULL + UNIQUE 제약 조건을 모두 포함하므로, 중복값을 INSERT하면 오류 발생

1-4. FOREIGN KEY (외래 키)

💡 다른 테이블의 PK에 참조 되는 KEY

💡 FOREIGN KEY 선언 방법
1. 서로 JOIN 할 필요가 있는 2개의 테이블을 선정
2. 2개의 테이블 간에 각각 개념적으로 몇 대 몇으로 매핑되는지 판단 (카디널리티)
3. 1:N 형식으로 매핑이 된다면, 1은 부모, N은 자식 테이블
4. 부모 테이블 에서 PK 를 자식 테이블에서 FK 로 참조되며 FK 선언

💡 FOREIGN KEY 의 특성
1. 참조 무결성 을 위배하지 않기 위해 사용
2. 참조(REFERENCES) 된 다른 테이블의 컬럼에 존재하는 값만 사용 가능( 그 외는 NULL만 사용 가능)
3. PK와 FK를 통해서 테이블 간의 관계(RelationShip) 형성
4. 삭제룰(FOREIGN KEY에 붙여서 씀) 에 따라 달라짐

• user_grade 테이블의 grade_code를 FK(FOREIGN KEY)로 가지는 user_forenignkey1 테이블의 grade code를 나타냄

1) 테이블 생성 (PK -FK 관계를 가진 2개의 테이블)

<코드>

-- 4-1. 회원용 부모 테이블 생성 (PK)
CREATE TABLE if NOT EXISTS user_grade (
  grade_code INT NOT NULL UNIQUE,
  grade_name VARCHAR(255) NOT NULL
);

-- 4-2. 회원용 자식 테이블을 생성 (FK)
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;

-- 조회
DESC user_foreignkey1;

<실행결과>

• 부모 테이블에 먼저 INSERT가 진행되어야 자식 테이블에 INSERT 가능
• FK를 선언하기 위해서는 2개의 테이블 간의 관계를 먼저 생각 (ex. 회원을 만들기 위해서는 회원의 grade정보를 담은 user_grade 테이블의 grade_code가 필요하다)
  
  

2) 참조된 컬럼에 없는 값(NULL)을 넣어보기

<코드>

-- 부모 테이블인 user_grade에 초기값 대입
INSERT
  INTO user_grade
VALUES
(10, '일반회원'),
(20, '우수회원'),
(30, '특별회원');

-- 자식 테이블인 user_foreignkey1에 초기값 대입
INSERT
  INTO user_foreignekey1
VALUES
(3, 'user01', 'pass01', '홍길동', '남', '010-111-2222', 'hong@gmail.com', NULL);

<실행결과>

• FK 에는 PK 에 해당하는 10,20,30 이 아닌 값들 중 NULL 값만 허용
  
  

3) 참조된 컬럼에 없는 값을 넣어보기

<코드>

-- 참조된 PK에 없는 값(NULL제외) 대입
INSERT
  INTO user_foreignekey1
VALUES
(4, 'user02', 'pass02', '유관순', '여', '010-333-2123', 'yu@gmail.com', 40);

<실행결과>

• 부모 테이블의 PK에 존재하지 않는 값 (NULL 제외) 을 대입하면 오류 발생
  
  

4) 참조된 부모테이블의 컬럼 삭제

<코드>

-- 자식 테이블에 참조되어 있는 값(10)
DELETE 
  FROM user_grade
 WHERE grade_code = 10;

<실행결과>

• 자식 테이블로 참조되어 있는 부모 테이블의 값은 바로 DELETE 불가 -> 자식 테이블의 참조된 값을 먼저 DELETE 필요
• 참조되어 있지 않은 값은 바로 삭제 가능
• 삭제하는 방식은 1) 자식테이블 삭제 -> 부모테이블 삭제 / 2) 제약조건 사용
  
  

4-1) 참조된 부모테이블의 컬럼 삭제( 자식테이블 먼저 삭제 )

<코드>

-- 자식 테이블 해당 컬럼 통으로 삭제
DELETE
  FROM user_foreignkey1
 WHERE grade_code = 10;
 
 -- 부모 테이블 해당 컬럼 통으로 삭제
 DELETE
   FROM user_grade
  WHERE grade_code = 10;

<실행결과>

• 참조되어 있는 자식 테이블을 삭제한 후 부모 테이블 삭제를 진행해야 함
• 하지만, 해당하는 한 행을 전체 삭제 해야하므로, 잘 사용하지 X
  
  

4-2) 삭제룰을 통해서 삭제하기 (ON DELETE CASCADE)

<코드>

-- 삭제룰(ON DELETE CASCADE)를 삽입한 테이블 생성
CREATE TABLE if NOT EXISTS user_foreignkey1 (
  user_no INT,
  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,
  PRIMARY KEY(user_no),
  FOREIGN KEY(grade_code) REFERENCES user_grade(grade_code)
  ON DELETE CASCADE
) ENGINE = INNODB;

-- 부모 테이블인 user_grade에서 삭제
DELETE
  FROM user_grade
 WHERE grade_code = 20;
 
 -- 조회
 SELECT * FROM user_foreignkey1;

<실행결과>

• 자식 테이블에 제약조건인 ON DELETE CASCADE 를 적용 후, 부모 테이블에 참조된 값을 삭제하면, 자식 테이블에서는 참조된 값에 해당하는 행이 다 삭제
• ON DELETE CASCADE 를 적용하는 테이블을생성할 시에는 PRIMARY KEY 를 Table_Level 에 선언해야 한다는 휴먼 에러 존재
• 한 행 자체가 삭제됨으로 잘 사용하지 X

4-3) 삭제룰을 통해서 삭제하기 (ON DELETE SET NULL)

<코드>

-- 삭제룰(ON DELETE SET NULL)을 삽입한 테이블 생성
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;

-- 테이블에 삽입
INSERT
  INTO user_foreignkey2
VALUES
(1, 'user02', 'pass02', '유관순', '여', '010-333-2123', 'yu@gmail.com', 10);

-- user_grade 부모 테이블과 연결되어 있는 기존 자식 테이블 초기화
TRUNCATE user_foreginkey1;

--참조하는 부모 테이블의 행 삭제 후 참조 컬럼 값 확인
DELETE FROM user_grade WHERE grade_code = 10;

-- 조회
SELECT * FROM user_foreignkey2;

<실행결과>

• FOREIGN KEY 에 삭제룰( ON DELTE SET NULL) 을 적용하여, 부모 테이블을 삭제하면 참조되어 있는 자식 테이블의 값이 NULL 로 바뀜
• 기존 부모 테이블과 연결되어 있는 자식 테이블을 초기화(TRUNCATE) 시킨 후 진행이 필요
  
  
  

1-5. Check

💡 위반시 오류가 발생하는 제약 조건

1) Check 제약조건에 맞는 값 대입

<코드>

-- 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)
) ENGINE = INNODB;

-- check 제약조건에 성립되는 값 대입
INSERT
  INTO user_check
VALUES
(NULL, '홍길동', '남', 25),
(NULL, '이순신', '남', 33);

<실행결과>

• Check 제약 조건에 성립하는 값을 대입하면 문제없이 잘 들어감
• Check 제약조건은 Tabel/Column-Level에 모두 들어갈 수 있다.
• Check 제약조건은 범위로도 지정 가능
  
  

2) Check 제약조건에 맞지 않는 값 대입

<코드>

-- 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)
) ENGINE = INNODB;

-- 성별에 잘못된 값 입력
INSERT
  INTO user_check
VALUES
(NULL, '아메바', '중', 19);

-- 나이 조건에 부합하지 X
INSERT
  INTO user_check
VALUES
(NULL, '유관순', '여', 16);

<실행결과>

• Check 제약조건에 성립하지 않는 값을 대입하면 오류가 발생
  
  
  

1-6. DEFAULT

💡 컬럼에 NULL값 대신 기본값(default)을 대입

<코드>

-- default 제약조건을 가진 테이블 생성
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 CHARSET UTF8;

-- NULL값 대신 DEFAULT값을 넣음
INSERT
  INTO tbl_country
VALUES
(NULL, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

-- 조회
SELECT * FROM tbl_country;

<실행결과>

• DEFAULT를 설정한 컬럼은 값을 넣지 않거나, DEFAULT를 넣으면 DEFAULT값으로 들어감
• 컬럼 타입이 DATE 이면 current_date만 가능
• 컬럼 타입이 DATETIME 이면 current_time, current_timestamp, now() 모두 가능