5.1 테이블 만들기

마우스로 클릭해서 만드는 GUI방식
직접 SQL문을 이용한 방식

# 데이터베이스와 sample테이블 만들어보기
CREATE DATABASE naver_db;
CREATE TABLE sample_table (num INT);

# 데이터베이스 삭제
DROP DATABASE IF EXISTS naver_db;


# 다시 만들고 사용
CREATE DATABASE naver_db;
USE naver_db;

# member 테이블 구성
DROP TABLE IF EXISTS member;  -- 기존에 있으면 삭제
CREATE TABLE member -- 회원 테이블
( mem_id        CHAR(8) NOT NULL PRIMARY KEY,   # primary key로 설정
  mem_name      VARCHAR(10) NOT NULL, 
  mem_number    TINYINT NOT NULL, 
  addr          CHAR(2) NOT NULL,
  phone1        CHAR(3) NULL,
  phone2        CHAR(8) NULL,
  height        TINYINT UNSIGNED NULL, 
  debut_date    DATE NULL
);

# buy 테이블 구성
DROP TABLE IF EXISTS buy;  -- 기존에 있으면 삭제
CREATE TABLE buy 
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- AUTO_INCREMENT를 통해 자동부여하고, 순번(PK)
   mem_id      CHAR(8) NOT NULL,  -- 아이디(FK)
   prod_name     CHAR(6) NOT NULL,  --  제품이름
   group_name     CHAR(4) NULL , -- 분류
   price         INT UNSIGNED NOT NULL, -- 가격
   amount        SMALLINT UNSIGNED  NOT NULL, -- 수량
   FOREIGN KEY(mem_id) REFERENCES member(mem_id)   # 위의 member테이블에서 mem_id와 엮어서 Foreign key 생성
);


# 구성한 각 테이블에 정보 주입
-- member tabel
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-8-8');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-1-15');
-- buy table
INSERT INTO buy VALUES( NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES( NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES( NULL, 'APN', '아이폰', '디지털', 200, 1);   # APN이라는 그룹이 member테이블에 존재하지 않음 -> 오류 발생

5.2 제약 조건으로 테이블을 견고하게

데이터의 오류를 줄여 완전무결한 코드를 만들자!

제약조건

데이터의 무결성을 지키기 위해 제한하는 조건
그렇다면, 데이터의 무결성이란? '데이터에 결함이 없음'을 의미

예를 들어 네이버 회원의 아이디가 중복되면 안됨 : 이러한 상황을 방지하기 위해 '기본 키' 즉, 'Primary key'를 지정하는 것이다.
기본 키의 조건은 '중복되지 않고, 비어 있지도 않음'이다.

이러한 경우 이외에도 다양한 제약조건이 존재하는데 한번 확인해보자!

• PRIMARY KEY 제약조건
  데이터를 구분할 수 있는 식별자를 기본키라고 부른다.
  값은 중복될 수 없으며, NULL값이 입력될 수 없다.
  ++ 기본키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.
  ++ 테이블은 기본 키를 1개만 가질 수 있다.

- PRIMARY KEY 생성 방법 1 
CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL
);

- PRIMARY KEY 생성 방법 2 : 우선적으로 만들고 마지막 줄에 PRIMARY KEY 선정해주기
CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL,
  PRIMARY KEY (mem_id)
);

- PRIMARY KEY 생성 방법 3 : 아예 테이블을 만들고 ALTER로 수정!
	( ADD CONSTANT는 제약조건을 추가한다는 의미 )
CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL
);
ALTER TABLE member
     ADD CONSTRAINT 
     PRIMARY KEY (mem_id);

• FOREIGN KEY 제약조건
  테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 한다.
  외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결된다!
  
  (우선적으로 PRIMARY KEY로 회원으로 등록해야 구매를 진행할 수 있다! 애초에 기본키로 등록되어 있지 않은데, 상품 구매를 할 수는 없다. 이를 통해 데이터의 무결성을 보장한다.)
  (외래 키 : 혼공SQL 교재)

DROP TABLE IF EXISTS buy, member;

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL
);

CREATE TABLE buy 
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id      CHAR(8) NOT NULL, 
   prod_name     CHAR(6) NOT NULL, 
   FOREIGN KEY(mem_id) REFERENCES member(mem_id)   # 위의 member테이블의 PRIMARY KEY와 연결해준다. - 중요한 것은 이해하기 쉽게 두 키의 이름을 같게 만든다.
);

이렇게 PK와 FK가 서로 연결이 된다면, 수정은 어렵다! 데이터의 무결성이 없어지며 혼란이 올 수 있기에..

++ 하지만 여기서 만약 애초에 기준 테이블의 열이 다를 경우는 어떻게 해야 할까?!
ex) 기준 테이블에서는 blackpink 로 아이디 이름이 되어 있는데, 참조 테이블에서는 BLK로 되어 있다면?!

=> ON UPDATE CASCADE / ON DELETE CASCADE

DROP TABLE IF EXISTS buy;

CREATE TABLE buy 
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id      CHAR(8) NOT NULL, 
   prod_name     CHAR(6) NOT NULL
);
ALTER TABLE buy
    ADD CONSTRAINT 
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;
    
INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');

UPDATE member SET mem_id = 'PINK' WHERE mem_id='BLK';

mem_id를 지정했음에도 BLK에서 PINK로 UPDATE가 됨을 확인할 수 있다!!

추가로 삭제도 가능하다!!!!
BUT!!! 참조테이블은 모두 삭제가 되기에 항상 주의!!

(참조테이블 삭제 : 혼공SQL 교재)

(모두 삭제된 것을 확인해 볼 수 있다.)

• UNIQUE 제약조건 : 고유 키 제약 조건
  고유 키의 제약조건은 '중복되지 않은 유일한 값'을 입력해야 하는 조건이다.
  기본 키와 비슷하지만, 차이점은 고유 키 제약조건은 NULL 값을 허용한다!
  또한 기본 키는 테이블에 1개만 설정해야 하지만, 고유 키는 어려 개를 설정해도 된다.

만약 회원 테이블에 Email주소가 있다면, 중복되지 않으므로 고유 키로 설정할 수 있다.

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL,
  email       CHAR(30)  NULL UNIQUE   # 고유 키로 지정
);

INSERT INTO member VALUES('BLK', '블랙핑크', 163, 'pink@gmail.com');   # 가능
INSERT INTO member VALUES('TWC', '트와이스', 167, NULL);   # 가능
INSERT INTO member VALUES('APN', '에이핑크', 164, 'pink@gmail.com');   # 고유값은 겹치면 안 되는데 이메일이 겹치기에 불가능!

• CHECK 제약조건
  체크 제약 조건은 입력되는 데이터를 점검하는 기능을 한다.
  예를 들어 평균 키에 마이너스 값이 입력되지 않도록 하거나, 국번에 02, 031, 041, 055 중 하나만 입력되도록 할 수 있다.

ex1) 키에 대한 제약
DROP TABLE IF EXISTS member;
CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL CHECK (height >= 100),   # 제약 조건을 걸어줌
  phone1      CHAR(3)  NULL
  
INSERT INTO member VALUES('BLK', '블랙핑크', 163, NULL);
INSERT INTO member VALUES('TWC', '트와이스', 99, NULL);   # 제약을 주었기에 입력이 되지 않는다!
);

ex2) 전화번호에 대한 제약 - 위의 테이블에 대해서 제약조건을 변경하는 코드를 작성해 보자!
ALTER TABLE member
    ADD CONSTRAINT 
    CHECK  (phone1 IN ('02', '031', '032', '054', '055', '061' )) ;

INSERT INTO member VALUES('TWC', '트와이스', 167, '02');   # 가능
INSERT INTO member VALUES('OMY', '오마이걸', 167, '010');   # 불가능

즉, 잘못된 데이터를 막을 수 있는 데이터의 무결성이 완성된다.

• DEFAULT 정의
  기본 값 정의란 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.

DROP TABLE IF EXISTS member;

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL DEFAULT 160,
  phone1      CHAR(3)  NULL
);

ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';   # phone1의 디폴트 값을 02로 지정하여 NULL이 들어왔을 때 02로 자동 입력이 되도록 한다.

• NULL 값 허용
  NULL이라고 쓰면 널 값을 허용하고,
  NOT NULL이라고 쓰면 널 값을 허용하지 않는다.

ex) '이름' 같은 경우를 NOT NULL을 통해 방지한다.