mysql 제약조건 알아보기 SQL 독학 강의#23편

Mysql 제약조건 알아보기 SQL 독학 강의#23편

 

콘텐츠 index 0. 제약조건 이란? 1. 제약조건의 종류 2. 각 제약조건의 설명 및 예제

## 전편 강의 보러 가기 ##

2020/06/23 - [Database/sql 강의] - MySQL data dictionary SQL 독학 강의#22편

MySQL data dictionary SQL 독학 강의#22편

Mysql에도 다른 DBMS들과 마찬가지로 제약조건이 있습니다.

 

이번 강의 시간에는 Mysql에서 사용할 수 있는 제약조건들을 확인하고, 해당 제약 조건이 어떤 역할을 하는지, 그리고 어떻게 추가하고 변경, 삭제할 수 있는지 알아보도록 하겠습니다.

 

 

0. 제약조건 이란?

제약조건이란 영어로 Constraint라고 합니다. DB내 테이블에 정해둔 어떤 규칙에 따라 올바른 데이터만 입력받고, 규칙에 어긋나거나 잘못된 데이터는 입력 및 변경이 되지 않도록 하는 기능을 하게 됩니다. 

 

특정 컬럼에 중복 값이 들어오지 못하게 한다던지, 상위 테이블에 등록된 값만 하위 테이블에 입력이 되도록 한다던지, 어떤 범위를 벗어난 값이 들어오지 못하게 한다던지 하는 것을 말하는 것 이죠.

 

이러한 제약조건의 역할로 인해 DB내 테이블이 가진 데이터의 정확성과 신뢰도가 올라갈 수 있습니다.

 

 

 

1. 제약조건의 종류

Mysql내에서 지원하는 제약조건은 크게 6가지가 있습니다.

primary key foreign key not null unique check default

제약조건은 테이블을 생성할때 DDL 문에 포함하여 생성할 수도 있고, 나중에 칼럼에 추가 및 삭제, 변경도 가능합니다.

가장 좋은건 설계 단계에서 모든 것이 고려되어 설계하면 좋으나, 사실 그렇게 되긴 현실적으로 어려울 수도 있습니다.

 

물론 primary key 같은 경우야 설계단계에서 얼마든지 요건의 정의 될 수 있으나 나머지는 변경이 일어날 가능성이 높습니다.

 

더불어 요즘은 실무에서 foreign key는 잘 적용하지 않는 추세 입니다. 그래도 알고는 있어야겠죠?

 

다음 꼭지에서 각각의 제약조건에 대한 설명 및 사용 예제를 안내드리도록 하겠습니다.

 

2. 각 제약조건의 설명 및 예제

primary key란 칼럼에 중복을 막고, null을 허용하지 않으며, 각 로우를 특정할 수 있는 구분키로 사용됩니다.

즉, not null + unique의 의미를 가지고 있습니다.

 

테이블 내 모든 데이터 간의 유일성을 보장하는 칼럼에 설정을 하게 되며, 테이블당 primary key는 하나만 생성이 가능합니다. 단, primary key를 구성하는 칼럼은 복수로 설정이 가능합니다.

 

※사용 예제

alter table schema_name.table_name
	add primary key (col1, col2, ....);
        
-- drop
alter table schema_name.table_name drop primary key;

primary key를 설정하실 때는 각 데이터 row의 유일성이 보장되어야 한다는 점 잊지 마시기 바랍니다.

 

primary key 예제 1)

위 테이블의 primary key는 jumin_no가 되겠죠. jumin_no는 주민등록번호로써 위 테이블의 각 row를 특정할 수 있기 때문입니다.

 

primary key 예제 2)

위 테이블의 primary key는 mgmt_num과 seq 칼럼입니다.

데이터를 잘 보시면 mgmt_num가 같은 것이 있고, 그 안에서 seq가 1부터 1씩 증가하는 것을 확인할 수 있습니다.

mgmt_num과 seq 두 칼럼이 함께 primary key로 설정되어야 하나의 데이터 row를 특정할 수가 있습니다.

 

foreign key는 어떤 테이블의 칼럼 값은 다른 테이블의 칼럼 값을 참조하여야 한다는 제약 조건입니다.

 

쉽게 설명하면 한 쇼핑몰의 주문 테이블이 있고, 고객 테이블이 있다면, 고객 테이블에 먼저 고객 정보가 저장이 되겠죠. 이때 등록되는 고객 ID값이 있는데, 나중에 주문테이블에 주문 정보가 들어올때 고객ID를 입력하여야 한다면, 주문테이블의 고객ID 컬럼에 들어오는 값은 고객테이블에 있는 고객ID 칼럼 중 한 값이 들어와야 한다는 것입니다.

 

반대로 이야기하면 고객 테이블의 고객 ID에 존재하지 않는 고객ID값은 주문 테이블의 고객ID 칼럼에 그 값이 들어올 수 없다는 것입니다.

 

상식적으로도 그게 맞겠죠. 고객이 아닌데, 주문을 할 수 없다면 말이죠.

바로 이런 것이 foreign key입니다.

 

※사용 예제

alter table order
	add constraint order_customer_id_fk
		foreign key (customer_id) references customer (customer_id);
        
-- drop
alter table order drop key order_customer_id_fk;

위 설명에서 주문과 고객 테이블 상황을 토대로 사용 예제 SQL을 작성했습니다.

그림을 통해서 좀 더 쉽게 이해할 수 있으시길 바랍니다.

 

not null은 말 그대로 해당 칼럼에는 null값이 들어올 수 없다고 제약조건을 명시하는것 입니다. 테이블내 컬럼중 어떤 일이 있어도 꼭 있어야 하는 정보가 담길 컬럼에 not null 제약 조건을 걸 수 있습니다.

 

주문 테이블을예를 들어 설명하자면, 옵션널한 칼럼(주문 시 요청사항, 선물포장 여부, 영문 이름 등등)의 값은 비어 있을 수 있겠지만, 꼭 필요한 주소, 수령자 이름, 주문 상품 ID 등등 꼭 필요한 정보가 들어가는 칼럼에 해당 제약조건을 설정할 수 있습니다.

 

※사용 예제

alter table schema_name.table_name modify col1 int not null;

-- not null 해제
alter table schema_name.table_name modify col1 int null;

not null 제약조건을 반영할 때는 칼럼의 데이터 타입도 변경될 수 있으니 이점 유의하셔야 합니다.

 

unique 제약조건은 설정된 칼럼에는 중복된 값이 들어가지 못하게 설정하는 제약조건입니다.

primary key와 혼동할 수도 있으나, primary key를 제외하고 테이블 내 다른 칼럼 중에 중복된 값이 들어오면 안 되는 경우에 설정할 수 있습니다.

 

※사용 예제

alter table schema_name.table_name
	add constraint table_pk
		unique (col1, col2);
        
-- drop
alter table schema_name.table_name drop key table_pk;

위 사용 예제처럼 칼럼을 두 개를 하나의 unique 제약조건에 설정할 수도 있는데, 이렇게 되면 하나씩 각각 중복 체크하는 게 아니라, 설정한 두 칼럼의 값이 모두 같아야 제약조건에 걸리게 됩니다.

 

unique 제약조건을 잘못 설정하게 되면 해당 테이블에 insert가 안 되는 상황이 발생할 수도 있으니, 조심하셔야 합니다.

 

check는 어떤 칼럼 값이 check 제약 조건으로 지정된 값 이외 다른 값이 들어오지 못하도록 하는 제약조건입니다. 

 

몇몇 개의 값만 들어오는 코드성 칼럼이나 여부, 유무 등의 Y, N 값만 들어와야 하는 칼럼, 성별 칼럼 등과 함께 어떤 범위 내의 값(온도 및 수량 등)만 들어오는 칼럼들에 설정을 하게 됩니다. 무결성을 보장하게 됩니다.

 

※사용 예제

ALTER TABLE schema_name.table_name
	ADD CONSTRAINT CHK_PersonAge CHECK (col1 >=18);

-- drop
ALTER TABLE schema_name.table_name
DROP CONSTRAINT CHK_PersonAge;

check 제약조건 역시 사용함에 있어서 신중하게 설정하셔야 합니다.

 

default는 어떻게 보면 제약조건이라고 부르기는 애매한 부분이 있습니다. 어떤 규칙을 가진다기 보단 초기값을 설정하는 것 이기 때문입니다.

 

default 제약조건이 설정된 칼럼은 특별한 값을 입력하지 않아도 미리 지정한 값이 기본적으로 row가 생길 때 들어가는 제약조건입니다.

 

특별한 경우나, 특정 이벤트가 발생할 때만 어떤 값이 변경되거나 입력되는 경우 해당 칼럼에 default 제약조건을 설정하여 기본값을 입력해두는 것입니다.

 

※사용 예제

alter table customer alter column name set default 'N';

-- drop
alter table customer alter column name drop default;

---

## 다음 강의 보러 가기 ##

2020/07/10 - [Database/sql 강의] - index 의 중요성과 이해 SQL 독학 강의#24편

index 의 중요성과 이해 SQL 독학 강의#24편

 

오늘은 이렇게 Mysql의 제약조건에 관해서 포스팅을 해보았습니다.

 

날씨가 많이 더워졌습니다. 그래서 저도 잠시 동안 좀 게을러져 있었는데.. 좀 정신 차려야 할 것 같습니다.

 

여러분들도 다가오는 여름 잘 대비하시고~~ 코로나 항상 조심합시다~!

 

감사합니다.

 

open.kakao.com/o/szfhqYec

Database/남/db개발님의 오픈프로필

 

 

by.sTricky