[SQL] 16. DDL, 제약 조건, VIEW

DDL(Data Definition Language / 데이터정의어)

• 데이터베이스의 설정이나 오브젝트의 생성(CREATE), 변형(ALTER), 삭제(DROP)를 할 수 있다.
• DDL의 문법은 기본적으로 하나의 문장이 하나의 트랜잭션으로 동작한다. 자동으로 COMMIT이 발생하기 때문에 기존의 진행 중이던 트랜잭션이 의도하지 않게 기록 될 수있으므로 DML 구문과는 혼용하지 말 것!

 

데이터베이스의 오브젝트 종류

테이블	데이터베이스의 데이터 저장의 기본 단위. 행으로 구성되어 있다.
뷰	테이블의 논리적인 부분 집합의 구조. 가상의 테이블의 오브젝트
인덱스	일부 쿼리의 성능을 올려주는 오브젝트. 보통은 자동 생성된 걸 사용

테이블 생성 - CREATE TABLE

• 새로운 테이블 구조를 생성하는 구문이다.
• 필수 요소 : 테이블명, 컬럼, 데이터타입, 컬럼 사이즈
• 옵션 요소 : 기본값(DEFAULT), 제약조건(CONSTRAINT)

 

CREATE DATABASE shopdb;

USE shopdb;

 

 

 

CREATE TABLE dept ( deptno int, dname varchar(14), loc varchar(13), create_date datetime DEFAULT now() );

DESC dept;

 

 

 

Q. 테이블 생성

• 테이블명students
• id 컬럼은 정수형이다.
• name은 문자열이고, 크기는 30바이트, 기본값으로 'Unknown'을 출력해야 한다.
• age는 정수형이다.

CREATE TABLE students ( id INT, name VARCHAR(30) DEFAULT 'Unknown', age INT );

DESC students;

 

 

 

 

제약조건

NOT NULL 제약조건

• NULL값을 허용하지 않는 제약조건
• 컬럼 레벨의 문법만 허용되는 제약조건

CREATE TABLE test1 ( id INT NOT NULL, name VARCHAR(30) NOT NULL, jumin VARCHAR(13) NOT NULL, job VARCHAR(20), email VARCHAR(20), phone VARCHAR(20) NOT NULL, start_date date );

DESC test1;

 

 

UNIQUE 제약조건

• 적용된 컬럼은 유일한 값을 가져야 하는 제약조건
• NULL값은 직접 비교가 불가능한 데이터로 UNIQUE로도 동일 값여부를 판단할 수 없어 여러 NULL의 값은 허용이 된다.
• 테이블에 PRIMARY KEY 제약조건이 없다면 처음오는 NOT NULL, UNIQUE가 같이 적용된 컬럼이 자동으로 PRIMARY KEY로 변환되어 적용된다.

 

CREATE TABLE test2 ( id INT NOT NULL UNIQUE, name VARCHAR(30) NOT NULL, jumin VARCHAR(13) NOT NULL UNIQUE, job VARCHAR(20), email VARCHAR(20) UNIQUE, phone VARCHAR(20) NOT NULL UNIQUE );

DESC test2;

 

PRIMARY KEY - 기본키

• PRIMARY KEY 제약조건은 테이블의 행을 고유하게 구분할 수 있는 식별자의 역할을 하는 컬럼의 제약조건이다.
• 기능적인 부분에서는 NOT NULL + UNIQUE 제약조건의 형태이지만 식별자로써 최적의 조건을 갖춘 컬럼에 적용되는 제약조건으로 의미 상으로 조금 더 검색의 기준으로 사용되기에 적절한 컬럼에 적용되는 제약조건이다.
• PRIMARY KEY 또한 INDEX가 자동 생성되어 검색에 사용되는 경우 좀 더 좋은 결과가 만들어진다.
• 테이블 당 한번만 사용되는 제약조건이다. 

 

CREATE TABLE test3 ( id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, jumin VARCHAR(13) NOT NULL UNIQUE, job VARCHAR(20), email VARCHAR(20) UNIQUE, phone VARCHAR(20) NOT NULL UNIQUE, start_date DATE );

DESC test3;

 

 

FOREIGN KEY 제약조건

• 외래키 제약조건이라고도 한다. 데이터의 일관성을 유지할 수 있도록 해주는 제약조건이다.
• 외래키는 PK, UK 제약조건의 컬럼의 데이터를 참조하도록 제한하는 제약조건이다.
• 부모(PK, UK) -- 자식(FK)의 관계로 설명하기도 한다.
• 자식(FK)은 부모가 물려준 값에 한해서만 사용하도록 제한한다.
• 부모가 가지고 있지 않은 값은 자식도 사용할 수 없다.
• 부모와 자식은 동일한 데이터타입과 크기를 갖추고 있어야 한다.
• 동일한 테이블에서도 부모, 자식의 관계가 있을 수 있음.
• 부모 - 자식 관계의 컬럼들은 데이터 조회 시 JOIN의 조건으로 활용하여 일관성이 있는 정보를 조회할 수 있다.

CREATE TABLE test4 ( t_num int PRIMARY KEY, t_id int, title varchar(20) NOT NULL, story varchar(100) NOT NULL, FOREIGN KEY(t_id) REFERENCES test3(id) );

INSERT INTO test3 (id, name, jumin, email, phone) VALUES (1, 'nname','1212', 'hhhgi@gm', '1122344'), (5, 'nname', '2222', 'hihi', '9999'), (10, 'nname', '1111', 'byebye', '101001');

SELECT * FROM test3;

 

• 두번째 컬럼인 t_id에 3의 값을 입력하는 경우 다음과 같은 에러가 발생한다.

INSERT INTO test4 (t_num, t_id, title, story) VALUES (2, 3, 'title2', 'sttory2');

 

• 부모인 test3의 id컬럼에는 1, 5, 10의 값만 있기 때문에 3의 값은 입력이 불가능하다.

 

 

 

CHECK 제약조건

• 사용자가 작성한 규칙을 만족하는 값만 컬럼에 저장할 수 있다.
• CHECK 제약조건의 규칙은 데이터 입력 시, 데이터 갱신에도 영향을 미친다.
• CHECK 제약조건은 컬럼 레벨과 테이블 레벨의 문법이 동일하다.

 

CREATE TABLE test5 ( id int(10) PRIMARY KEY, name varchar(30) NOT NULL, jumin varchar(13) NOT NULL UNIQUE CHECK (LENGTH(jumin) = 13),  ← 컬럼 레벨 문법 job varchar(20), email varchar(20) UNIQUE, phone varchar(20) NOT NULL UNIQUE, start_date date, CHECK (start_date >= '2005-01-01')  ← 테이블 레벨 문법 );

INSERT INTO test5 (id, name, jumin, phone, start_date) VALUES (1, 'NULL', 'qwertyuiopas1', '1234','2005-01-02');

SELECT * FROM test5;

• jumin 컬럼의 입력값이 check 제약조건 CHECK (LENGTH(jumin) = 13) 에서 길이가 13이 안되어 제약조건 위배로 입력이 실패되었음.

INSERT INTO test5 (id, name, jumin, phone, start_date) VALUES (2, 'NULL', 'qwertyuiop', '12345','2005-01-02');

 

 

 

• start_date의 입력 값이 제약조건 CHECK (start_date >= '2005-01-01') 을 만족하지 않아 입력 실패되었음.

INSERT INTO test5 (id, name, jumin, phone, start_date) VALUES (2, 'NULL', '1234567891012', '12345','2004-01-02');

 

 

SELECT * FROM table_constraints WHERE table_name = 'TEST5';

 

CONSTRAINT_CATALOG|CONSTRAINT_SCHEMA|CONSTRAINT_NAME|TABLE_SCHEMA|TABLE_NAME|CONSTRAINT_TYPE|ENFORCED|

------------------+-----------------+---------------+------------+----------+---------------+--------+

def               |shopdb           |email          |shopdb      |test5     |UNIQUE         |YES     |

def               |shopdb           |jumin          |shopdb      |test5     |UNIQUE         |YES     |

def               |shopdb           |phone          |shopdb      |test5     |UNIQUE         |YES     |

def               |shopdb           |PRIMARY        |shopdb      |test5     |PRIMARY KEY    |YES     |

def               |shopdb           |test5_chk_1    |shopdb      |test5     |CHECK          |YES     |

def               |shopdb           |test5_chk_2    |shopdb      |test5     |CHECK          |YES     |

 

서브쿼리로 테이블 생성하기

• 서브쿼리의 출력 값을 복사하여 새로운 테이블을 생성하는 문법이다.
• 새롭게 생성된 테이블의 데이터 타입과 데이터값, 일부 제약조건(NN)은 서브쿼리의 베이스 테이블의 정보를 가져와서 생성하게 된다.
• 서브쿼리 작성 시 표현식이나 함수 형태의 컬럼은 테이블의 컬럼으로 사용할 수 없는 문자도 포함하기 때문에 column alias를 필수적으로 적용해야 한다.
• where절의 조건에 따라 복사되는 데이터가 정해진다.

use hr;

CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 AS annsal, hire_date FROM employees WHERE department_id = 80;

 

 

연습문제 풀이

CREATE TABLE title ( title_id INT PRIMARY KEY, title VARCHAR(60) NOT NULL, description VARCHAR(400) NOT NULL, rating VARCHAR(4) CHECK (rating IN ('G', 'PG', 'R', 'NC17', 'NR')), category VARCHAR(20) CHECK (category IN ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY')), release_date DATE );

SELECT *

FROM information_schema.table_constraints

WHERE table_name = 'TITLE';

SELECT *

FROM information_schema.check_constraints;

 

 

 

CREATE TABLE title_copy ( copy_id INT, title_id INT REFERENCES title(title_id), <-FK 제약 조건 status VARCHAR(15) NOT NULL CHECK (status IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED')), PRIMARY KEY(copy_id, title_id) <- PK );

 

 

AUTO_INCREMENT 옵션

• 테이블의 컬럼에 적용되는 옵션 설정이다.
• AI가 적용된 컬럼은 기본적으로 숫자 1부터 1씩 증가하는 값을 컬럼에 자동으로 입력해준다.

CREATE TABLE test_ai ( id INT PRIMARY KEY AUTO_INCREMENT );

 

• 명시적, 암시적으로 NULL값을 입력하게 되면 자동으로 사용된 다음 숫자값을 만들어 채워준다.

INSERT INTO test_ai VALUES (NULL);

id|

--+

 1|

 2|

 3|

 4|

 5|

 6|

 7|

 

• 다만, 다음과 같이 값을 직접 입력하게 되면 기존 순서와 상관없이 입력값을 허용한다.

INSERT INTO test_ai VALUES (10);

 

id|

--+

 1|

 2|

 3|

 4|

 5|

 6|

 7|

10|

 

 

VIEW

• 값을 직접 저장하지 않고 select 구문의 결과값을 가상의 테이블처럼 사용할 수 있도록 해주는 오브젝트

 

VIEW 생성(CREATE VIEW)

CREATE OR REPLACE VIEW view이름 AS 서브쿼리 ... ;

• OR REPLACE : VIEW의 경우 ALTER로 수정하는 문법은 없기 때문에 수정이 필요한 경우 OR REPLACE로 기존 오브젝트를 바로 대체하여 만드는 방식을 사용한다.

 

CREATE OR REPLACE VIEW dept30 AS SELECT employee_id, last_name, department_id FROM employees WHERE department_id = 30;

SELECT * FROM dept30;

employee_id|last_name |department_id|

-----------+----------+-------------+

        114|Raphaely  |           30|

        115|Khoo      |           30|

        116|Baida     |           30|

        117|Tobias    |           30|

        118|Himuro    |           30|

        119|Colmenares|           30|

 

 

VIEW 삭제(DROP VIEW)

DROP VIEW dept30;