MySQL 제약조건

테이블을 만들 때는 테이블의 구조에 필요한 제약조건을 설정해야 한다.

MySQL테이블 글에서 사용한 기본키(primary key)와 외래키(foreign key)가 대표적인 제약조건이다.

 

제약조건의 기본 개념

제약조건(constraint)은 데이터의 무결성을 지키기 위해 제한하는 조건이다.

데이터의 무결성이란 데이터에 결함이 없다는 의미이다.

ex) 네이버의 회원 아이디가 중복되면 네이버의 모든 서비스 이용에 혼란이 일어난다.

위의 예시와 같은 결함이 없는 것을 데이터의 무결성 이라고 표현한다.

예시 상황같은 경우에는 데이터의 무결성을 위해서 회원 테이블의 아이디를 기본키로 지정할 수 있다.

기본키로 설정을 하게되면 중복된 아이디를 실수로 입력하려 해도 입력이 불가능하기 때문에 값이 중복되지 않게된다.

 

제약 조건의 종류

1.primary key

테이블의 많은 데이터 중에 데이터를 구분할 수 있는 식별자이다.

ex) 회원 아이디, 학생의 학번, 직원의 사번 등이 있다.

 

기본키의 값은 중복될 수 없으며 null값 또한 입력될 수 없다.

대부분의 테이블은 기본키를 가져야 하며 테이블당 기본키는 1개만 가질 수 있다.

만약 아이디, 주민등록번호, 이메일 처럼 기본키로 설정할 수 있는 열이 여러개인 경우에는 테이블의 특성을 가장 잘 반영하는 열을 선택하는 것이 좋다.

# 방법1 : 생성하면서 컬럼에 옵션값으로 기본키 제약조건 설정하기
create table member(
	mem_id char(8) not null primary key,
	mem_name varchar(10) not null,
	height tinyint unsigned null
);

# 방법2 : 생성하면서 마지막에 기본키 제약조건 설정하기 
create table member(
	mem_id char(8) not null,
	mem_name varchar(10) not null,
	height tinyint unsigned not null,
	primary key (mem_id)
);

# 방법3 : 먼저 테이블을 생성한 이후 테이블 수정을 통해 기본키 제약조건 설정하기
create table member(
	mem_id char(8) not null,
	mem_name varchar(10) not null,
	height tinyint unsigned not null
);

alter table member -- member 테이블을 수정
	add constraint -- 제약조건을 추가
	primary key (mem_id); -- mem_id 에 기본키 제약조건을 설정

 

2. foregin key

외래키 라고 부르며 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장하기 위해서 사용한다.

외래키가 설정된 열은 다른 테이블의 기본키와 연결된다.

MySQL 글들에 자주 사용되었던 member 테이블과 buy 테이블이 기본키-외래키 관계이다.

이 경우 기본키가 있는 member 테이블을 기준 테이블 이라고 하며, 외래키가 있는 buy 테이블을 참조 테이블 이라고 한다.

구매 테이블의 FK(foregin key)는 반드시 회원 테이블의 PK(primary key)로 존재하게 된다.

# 방법1 : 생성하면서 바로 외래키 설정하기
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)
);

# 방법2 : 생성한 이후 테이블 수정으로 외래키 설정하기
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);

 

데이터 입력하기

insert into member values("BLK", "블랙핑크", 163);
insert into buy values(null, "BLk", "지갑");
insert into buy values(null, "BLk", "맥북");

 

데이터의 값 수정하기

update member set mem_id = "PINK" where mem_id = "BLK";

기본키와 외래키로 맺어진 후에는 기존 테이블의 열 이름이 변경되지 않아 오류를 발생한다.

그 이유는 열의 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문이다. 따라서 지금은 회원 테이블의 BLK가 물건을 구매한 기록이 구매 테이블에 존재하기 때문에 값의 변경이 불가능하다.

하지만 만약 BLK가 물건을 구매한 적이 없다면(구매 테이블에 데이터가 존재하지 않는다면) 회원 테이블의 BLK는 변경이 가능하다.

 

데이터 삭제하기

delete from member where mem_id = "BLK";

기본키와 외래키로 관계가 맺어지면 기존 테이블의 열은 수정이나 삭제가 되지 않는다.

하지만 기존 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 삭제되거나 변경 된다면 더 효율적일 것이다.

따라서 이 기능을 위해 on update cascade와 on delete cascade를 사용할 수 있다.

 

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", "맥북");

 

on update cascade 설정 이후 member 테이블의 데이터 수정하기

update member set mem_id = "PINK" where mem_id = "BLK";

sql문을 실행한 이후 결과를 확인하면 값이 모두 변경된것을 확인할 수 있다.

삭제 또한 가능하다.

 

3. unique

고유키 라고 하며 중복되지 않는 유일한 값을 입력해야 하는 조건이다.

기본키 제약조건과 거의 유사하지만 고유키 제약조건은 null 값을 허용한다는 점이 다르다, 또한 기본키는 테이블당 1개만 설정할 수 있었지만 기본키는 여러개를 설정해도 아무런 이상이 없다.

참고로 null값은 여러개가 입력되어도 상관 없다.

ex) 회원 테이블의 email 주소가 있다면 중복되지 않으므로 고유키로 설정할 수 있다.

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

마지막에 입력하려고 하는 에이핑크는 이메일이 중복되었기 때문에 오류가 발생한다.

 

4. check

체크 제약조건은 입력되는 데이터를 점검하는 기능이다.

ex) height에 마이너스 값이 입력되지 않도록 하거나, 연락처의 국번이 02, 031, 055 중 하나만 입력되도록 정의 하는 경우

 

height가 반드시 100이상의 값만 입력되도록 체크 제약조건을 추가하기

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

처음 블랙핑크는 아무이상 없이 값의 입력이 잘 되었으나 트와이스는 height가 체크 값보다 작기 때문에 오류가 발생한다.

 

5. default

기본값은 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정하는 방법이다.

ex) height를입력하지 않고 기본적으로 160이라고 입력되도록 하고 싶은 경우

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

 

데이터 추가하기

insert into member values("RED", "레드벨벳", default, "054");

위의 sql을 실행하면 default로 지정해둔 열에는 160이라는 값이 자동으로 들어간 후 저장된다.

 

null

null값을 허용하려면 null을 입력하거나 생략할 수 있고, 허용하지 않으려면 not null을 작성하면 된다.

primary key가 설정된 열에는 null값이 있을 수 없으므로 생략하면 자동적으로 not null로 인식한다.

null은 '아무것도 없다' 라는 의미로 공백("")이나 0과는 다르다.