제약조건 및 키(Key)

1. 제약조건(constraint)

데이터의 무결성을 지키기 위한 제한된 조건을 의미한다.

즉, 특정 데이터를 입력할때 무조건적으로 입력되는 것이 아닌 어떠한 조건을 만족했을 때에 입력되도록 제약할 수 있다.

MySQL은 데이터의 무결성을 위해서 다음의 5가지의 제약조건을 제공한다

- PRIMARY KEY 제약조건

- FOREIGN KEY 제약조건

- UNIQUE 제약조건

- DEFAULT 정의

- NULL 값 허용

다른 DBMS에서는 추가로 CHECK 제약조건을 제공하지만 MySQL은 CHECK 제약조건을 지원하지 않는다.

A. 기본키 제약조건

- 테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자를 '기본 키'라고 부른다.

- 기본키에 입력되는 값은 중복될 수 없으며 NULL값이 입력될 수 없다.

테이블 제약조건 정보 보기

describe departments;

기본키 생성 예시

create table prodTbl
( prodCode char(3) not null,
prodID char(4) not null,
prodDate DATETIME not null,
prodCur char(10) null
);
 
alter table prodTbl
add constraint pk_prodTbl_proCode_prodID
primary key (prodcode, prodID); -- 제품코드 + 제품 일렬 번호 복합으로 기본키 생성

또는

create table prodTbl
( prodCode char(3) not null,
prodID char(4) not null,
prodDate DATETIME not null,
prodCur char(10) null,
constraint pk_prodTbl_proCode_prodID -- pk이름
primary key (prodCode, prodID)
);

show keys from 테이블이름; 사용하면 테이블에 지정된 키를 볼수있다.

---

B. 외래 키 제약 조건

두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장해주는 역할을 한다.

외래 키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존하게 된다.

create table prodTbl
( prodCode char(3) not null,
prodID char(4) not null,
prodDate DATETIME not null,
prodCur char(10) null,
constraint fk_usertbl_buytbl foreign key(prodID) references usertbl(userid)
);
-- constraint fk_usertbl_buytbl foreign key(prodID) references usertbl(userid)
-- fk_usertbl_buytbl : 외래키 이름 지정
-- prodID : 테이블생성시 지정할 컬럼명
-- usertbl(userid) : usertbl 테이블의 userid를 참고

또는

create table prodTbl
( prodCode char(3) not null,
prodID char(4) not null,
prodDate DATETIME not null,
prodCur char(10) null
);
 
alter table prodTbl
add constraint fk_usertbl_buytbl
foreign key(prodID)
references usertbl(userid);

on delete cascade 와 on update cascade옵션이 있다. 이것은 상위테이블 데이터 변경시 연결된 하위테이블 데이터가 값이 자동으로 변경된다.

기본값은 on update no action 및 on delete no action이다.

create table prodTbl
( prodCode char(3) not null,
prodID char(4) not null,
prodDate DATETIME not null,
prodCur char(10) null
);
 
alter table prodTbl
add constraint fk_usertbl_buytbl
foreign key(prodID)
references usertbl(userid)
(on delete cascade | on update cascade);

---

C. UNIQUE 제약조건

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

PRIMARY KEY와 비슷하지만 차이점은 UNIQUE은 NULL값을 허용한다는 것이다.

create table prodTbl
( prodCode char(3) not null,
prodID char(4) not null,
prodDate DATETIME not null,
prodCur char(10) null unique
-- constraint ak_prodcur unique (prodcur)
);

---

D. DEFAULT 정의

값을 입력하지않아도 자동으로 입력되는 기본값을 정의해줄수 있따.

create table prodTbl
( prodCode char(3) not null,
prodID char(4) not null,
prodComent char(255) not null default '',
prodCur char(10) null unique
);

create table prodTbl
( prodCode char(3) not null,
prodID char(4) not null,
prodComent char(255) not null,
prodCur char(10) null unique
);
 
alter table prodtbl
	alter column prodComent set DEFAULT '';

---

임시 테이블

- 임시 테이블은 세션(session)내에서만 존재하며 세션이 닫히면 자동으로 삭제된다. 또한 임시 테이블은 생성한 클라이언트에서만 접근이 가능하며 다른 클라이언트에는 접근할 수 없다.

- 임시 테이블은 데이터베이스 내의 다른 테이블과 이름을 동일하게 만들 수 있다. 그러면 기존의 테이블은 임시 테이블이 있는 동안에 접근이 불가능하다.

- 임시 테이블 삭제되는 시점

1) 사용자가 drop table로 직접 삭제

2) workbench를 종료하거나 mysql 클라이언트를 종료하면 삭제됨

3) mysql 서비스가 재시작되면 삭제됨

create temporary table [if not exists] 테이블 이름
(열 정의 ...)

---

테이블 삭제

drop table 테이블 이름;

---

테이블 수정

-- 열추가
alter table 테이블이름
add 컬럼명이름 변수형식
default 'abcd'
null;
 
-- 열삭제
alter table 테이블이름
drop column 컬럼명
 
-- 열이름 및 데이터 형식 변경
alter table 테이블이름
change column 기존컬럼명 새이름 변수형식;
 
-- 열제약 조건 추가 및 삭제
alter table 테이블이름
drop primary key;
 
alter table 테이블이름
drop foreign key 외래키이름;
 
-- 외래키 비활성화
set foregin_key_checks = 0;