1. CONSTRAINTS

• 데이터 무결성을 보장하는데 도움이 됨
• 제약조건으로 테이블에 데이터가 입력되거나 수정될 때의 규칙을 정의

1. NOT NULL

• 컬럼 레벨에만 작성
• NULL값을 허용하지 않는 제약조건

-- 1. not null 제약조건

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;

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', 'yu77@gmail.com');

-- user_id를 null로 했을 경우(에러 발생)
INSERT 
  INTO user_notnull
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(4, null, 'pass03', '유관순', '여', '010-777-7777', 'yu77@gmail.com');

SELECT * FROM user_notnull;

2. UNIQUE

• 컬럼 레벨에도 작성할 수 있고, 테이블 레벨 밑에도 작성 가능
• 중복값 허용하지 않는 제약조건

-- 2. UNIQUE 제약조건

DROP TABLE IF EXISTS user_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;

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');

SELECT * FROM user_unique;

-- 전화번호 중복(에러발생)
INSERT
  INTO user_unique
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(3, 'user03', 'pass03', '강감찬', '남', '010-777-7777', 'yu87@gmail.com');

3. PRIMARY KEY

• PK = PRIMARY KEY의 줄임말
• 테이블에서 한 행의 정보를 찾기 위해 사용 할 컬럼을 의미한다.
• 테이블에 대한 식별자 역할을 한다.(한 행씩 구분하는 역할을 한다.)
• NOT NULL + UNIQUE 제약조건의 의미 ⇒절대 중복되지 않고 NULL값 허용되지 않기 때문에 인덱스 역할을 함
• 한 테이블당 한 개만 설정할 수 있음
• 컬럼 레벨, 테이블 레벨 둘 다 설정 가능함
• 한 개 컬럼에 설정할 수도 있고, 여러 개의 컬럼을 묶어서 설정할 수도 있음(복합키): 복합키는 테이블 레벨 설정만 가능
• 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기

-- 3. PRIMARY KEY 제약조건
DROP TABLE IF EXISTS user_primarykey;
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),
    PRIMARY KEY (user_no)
) ENGINE=INNODB;

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');

SELECT * FROM user_primarykey;

-- primary key 제약조건 에러 발생(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 제약조건 에러 발생(중복값 적용)
INSERT 
  INTO user_primarykey
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(2, 'user03', 'pass03', '이순신', '남', '010-777-7777', 'lee222@gmail.com');

4. FOREIGN KEY

• 삭제룰 관련 링크 https://mariadb.com/kb/en/foreign-keys/
  
• 외래키 제약조건
• FK = FROEIGN KEY의 줄임말
• 다른 테이블에 있는 PK 값 + NULL 값까지 들어갈 수 있게 제약을 거는 것
• 참조 무결성을 위배하지 않기 위해 사용
• 참조(REFERENCES)된 다른 테이블에서 제공하는 값만 사용할 수 있음
• FOREIGN KEY 제약조건에 의해서 테이블 간의 관계(RELATIONSHIP)가 형성 됨
• 제공되는 값 외에는 NULL을 사용할 수 있음
• 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기(부모 테이블)
• RDBMS는 관계를 지향하는데, FK를 사용함으로서 관계가 형성됨

-- 4. FOREIGN KEY 제약조건

-- 4-1. 회원등급 부모 테이블 먼저 생성
DROP TABLE IF EXISTS user_grade;
CREATE TABLE IF NOT EXISTS user_grade (
    grade_code INT NOT NULL UNIQUE,
    grade_name VARCHAR(255) NOT NULL
) ENGINE=INNODB;

INSERT 
  INTO user_grade
VALUES 
(10, '일반회원'),
(20, '우수회원'),
(30, '특별회원');  -- grade_code = 부모테이블 / 등급이 존재하는 것만 참조해야 함

SELECT * FROM user_grade;

• 부모 테이블인 user_grade에 있는 grade_code의 코드가 10, 20 모두 정상적으로 추가됨

-- 4-2. 회원 자식 테이블(부모 테이블 이후에 생성)
-- 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기2
-- (자식 테이블 - DELETE 삭제룰 없을 시 )

DROP TABLE IF EXISTS user_foreignkey1;
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) -- 2. 내 테이블에서 이용할 겁니다.
		REFERENCES user_grade (grade_code) -- 1. 부모테이블에 있는 컬럼을
) ENGINE=INNODB;

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

SELECT * FROM user_foreignkey1;

• 부모테이블에 없는 등급으로 insert하면 오류 나옴

-- 부모테이블에 없는 등급으로 insert
INSERT 
  INTO user_foreignkey1
(user_no, user_id, user_pwd, user_name, gender, phone, email, grade_code)
VALUES
(3, 'user03', 'pass03', '이순신', '남', '010-777-7777', 'lee222@gmail.com', 50);

-- 자식테이블에서 foreign key 제약조건 컬럼 null값으로 insert
INSERT 
  INTO user_foreignkey1
(user_no, user_id, user_pwd, user_name, gender, phone, email, grade_code)
VALUES
(3, 'user03', 'pass03', '이순신', '남', '010-777-7777', 'lee222@gmail.com', NULL); -- 자식이 null값을 가지고 있는 데이터만큼은 부모테이블과 연결 끊김

• 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기3
  (자식 테이블 - DELETE 삭제룰 있을 시 )

-- 5. 삭제룰을 적용한 foreign key(외래키) 제약조건
DROP TABLE IF EXISTS user_foreignkey2;
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 UPDATE SET NULL 
        ON DELETE SET NULL-- 삭제 룰
) ENGINE=INNODB;

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

SELECT * FROM user_foreignkey2;

-- --------------------------------------------------------------------------------------

-- 부모 테이블에서 우수 회원 등급 삭제 
SELECT * FROM user_grade;
DELETE FROM user_grade WHERE grade_code = 20; -- user_foreignkey1 테이블은 삭제룰을 적용하지 않고 20을 참조한 데이터가 있다.

-- 해결 방법
DELETE FROM user_foreignkey1 WHERE 1=1; -- user_foreignkey1 테이블의 참조하는 데이터 삭제

SELECT * FROM user_foreignkey2;
DELETE FROM user_grade WHERE grade_code = 20; -- user_foreignkey1 테이블은 삭제룰이 없기에 삭제룰이 없는 자식테이블을 먼저 삭제 후 부모 테이블을 삭제

-- 부모 테이블에 새로운 등급 추가
INSERT 
  INTO user_grade 
VALUES (40, '에메랄드 회원');

-- null값으로 변화된 자식 테이블의 행들을 새로 추가한 회원 등급으로 업데이트
UPDATE user_foreignkey2
	SET grade_code = 40
 WHERE grade_code IS NULL;

-- 확인
SELECT * FROM user_foreignkey2;

• 자식이 참조되어 있으면 부모 테이블은 함부로 건들 수 없음
• 건들이려면 삭제 룰을 적용해야 함

5. CHECK

• 테이블 레벨에 달아도 컬럼 레벨에 동일하게 쓸 수 있음
• where 절에 쓸 수 있는 조건들을 쓰면 됨
• check 제약 조건 위반 시 허용하지 않는 제약 조건

-- 6. check 제약조건(조건식을 활용한 구체적인 제약조건 / true or false가 나올 수 있는 논리 연산이나 비교 연산)
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, '홍길동', '남', 25),
(null, '이순신', '남', 33);

SELECT * FROM user_check;

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

-- 나이에 잘못된 값 입력해보기
INSERT
  INTO user_check
VALUES
(NULL, '유관순', '여', 16);

6. DEFAULT

• 컬럼 타입이 DATE일 시 current_date만 가능
• 컬럼 타입이 DATETIME일 시 current_time과 current_timestamp, now() 모두 사용 가능

-- 7. default 제약조건
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;

SELECT * FROM tbl_country;

-- default 설정이 되어 있는 컬럼들에 default 값이 들어가도록 INSERT 진행 후 조회
-- default로 insert 또는 update를 하면 default로 초기 세팅된 값으로 적용된다.
INSERT 
  INTO tbl_country
VALUES (null, default, default, default, default);

-- insert시 default 설정된 컬럼을 무시하고 insert하면 null이 아닌 default로 초기 세팅된 값이 들어간다.
INSERT 
  INTO tbl_country
  (country_code)
VALUES (null);

SELECT * FROM tbl_country;