제약조건
테이블에 부적합한 데이터가 삽입/수정/삭제되는 것을 막아줌.
- 제약조건 유형
- not null
- unique
- primary key
- foreign key
- check
- 제약조건 선언 시기
테이블 생성 시 - create table ---;
테이블 생성 후 - alter table ---;- 제약조건 선언 문법
컬럼 레벨 문법 : nn, uk, pk, ck
테이블 레벨 문법 : fk, uk, pk, ck
- 제약조건 선언 문법
[제약조건1] 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;
[제약조건2] unique
중복값이 삽입/수정되는 것을 막아주는 제약조건
- 고유한 값이 들어와야하는 컬럼에 사용함.
- (단, not null 제약조건이 선언되어 있지 않다면 null 값은 허용함)
- (예) 주민번호, 전화번호, 메일 등
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,
start_date date);
desc test2;
[제약조건3] primary key(기본키)
not null + unique의 성격을 가지고 있는 제약조건
- null값 및 중복값이 삽입/수정되는 것을 막아주는 제약조건
- 단, 테이블에 한번만 선언할 수 있음!!
- 학번, 사번, 회원번호, 제품번호 등
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;
[제약조건4] foreign key(외래키)
자기자신 테이블이나 다른 테이블의 특정 컬럼(PK, UK)을 첨조하는 제약조건
- FK 제약조건이 선언된 컬럼 : 자식컬럼
- FK 제약조건이 참조하는 컬럼 : 부모컬럼
- 자식 컬럼에는 부모 컬럼에 있는 값 중 하나만 삽입/수정 될수 있음!
- (단, not null 제약조건이 선언되어 있지 않는 경우 null값은 허용함)
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) );
desc test4;
- MUL : foreign key
[제약조건5] check
해당 컬럼이 만족해야하는 조건문을 자유롭게 지정하는 제약조건
- (예)
- salary int check (salary > 0)
- 성별 varchar(10) check (성별 in('남','여'))
- jumin varchar(13) check (length(jumin)=13) -> 12,11자리 입력X
- email varchar(50) check (email like '%@%')
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'));
desc test5;
Data Dictionary(DB 사전) 사용하기
show databases;
- information_schema : DB에 대한 정보를 볼수 있는 DB 사전
DB 사전인 information_schema로 전환하기
use information_schema;
show tables;
check_constraints 체크 제약 조건
columns 컬럼 정보
table_contraints 테이블마다 선언된 제약조건
tables 테이블들에 대한 정보desc table_constraints;
어떤 DB에 어떤 제약조건이 있는지 확인할수 있음
select table_schema, table_name, constraint_type
from table_constraints
where table_schema = 'hr'
order by table_name;
- not null은 조회 안됨
- check 조건문 확인
desc check_constraints;
select *
from check_constraints
where constraint_schema = 'hr';
서브쿼리를 활용한 테이블 생성
기본 테이블(서브쿼리 테이블)의 복사본 테이블이 생성됨.
제약조건은 not null 제약조건만 복사
- 예제1
use hr;
create table dept80
as select employee_id, last_name, salary*12 as annsal, hire_date
from employees
where department_id = 80;
desc dept80;
select *
from dept80;
- 예제2 백업용 또는 테스트용으로 복사본 테이블 생성 많이함.
create table copy_dept
as select *
from departments;
desc copy_dept;
select *
from copy_dept;
연습문제
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);
desc title;
create table TITLE_COPY
(COPY_ID int,
TITLE_ID int,
STATUS varchar(15) not null check(STATUS in ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED')),
foreign key(TITLE_ID) references TITLE(TITLE_ID),
primary key(COPY_ID, TITLE_ID));
desc title_copy;
제약조건은 1가지만 보여짐
DB사전으로 상세히 조회 가능
