[혼공SQL] 5-2 제약조건으로 테이블을 견고하게

본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.

 

1. PRIMARY KEY 제약조건

• 데이터를 구분할 수 있는 식별자
• 중복 x
• NULL 값 입력 x
• 테이블은 기본 키(PK)를 1개만 가질 수 있음
• 기본 키(PK)로는 테이블의 특성을 가장 잘 반영하는 열을 선택하는 것이 좋음

 

1.1 CREATE TABLE 에서 설정하는 기본 키 제약조건

1) 열 이름 뒤에 primary key 를 붙여줌

create table member
( mem_id   char(8)     not null **primary key**,
  mem_name varchar(10) not null,
  height   tinyint unsigned null
  );

 

2) 제일 마지막 행에 primary key (mem_id) 추가

create table member
( mem_id   char(8) not null,
  mem_name varchar(10) not null,
  height   tinyint unsigned null,
  **primary key (mem_id)**
  );

 

+) DESCRIBE 문을 사용하여 테이블 정보 확인

describe member;

 

1.2 ALTER TABLE 에서 설정하는 기본 키 제약조건

• 이미 만들어진 테이블을 수정하는 ALTER TABLE 구문 사용

drop table if exists member;

create table member
( mem_id char(8) not null,
  mem_name varchar(10) not null,
  height tinyint unsigned null
  );

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

 

2. FOREIGN KEY 제약조건

• 두 테이블(기준 테이블 - 참조 테이블) 사이의 관계를 연결
• 그 결과, 데이터의 무결성을 보장해주는 역할
• 외래키가 설정된 열은 다른 테이블의 기본 키와 연결됨

 

2.1 CREATE TABLE 에서 설정하는 외래 키 제약조건

• 형식 : FOREIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름)
• 기준 테이블의 열이 primary key 또는 Unique가 아니라면 외래 키 관계는 설정되지 않음

create table member
( mem_id   char(8)     not null primary key,
  mem_name varchar(10) not null,
  height   tinyint unsigned null
  );  

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

💡 참조 테이블(buy)의 아이디 열 이름과 기준 테이블(member)의 아이디 열 이름이 달라도 상관없음!

      → foreign key(mem_id) references member(user_id)

 

2.2 ALTER TABLE 에서 설정하는 외래 키 제약조건

drop table if exists 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                  -- buy 테이블을 수정
	add constraint                 -- 제약조건 추가
    foreign key(mem_id)          -- buy 테이블의 mem_id에 fk 제약 조건 설정
    references member(mem_id);   -- member 테이블의 mem_id 열을 참조**

 

2.3 기준 테이블의 열이 변경될 경우

• 내부 조인을 사용해서 물품 정보 및 사용자 정보를 확인

select M.mem_id, M.mem_name, B.prod_name
	from buy B
		inner join member M
			on B.mem_id = M.mem_id;

 

• BLK 아이디를 PINK로 변경 → 오류 발생
  • 기본 키-외래 키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않음
  • 열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문

 

update member set mem_id = 'pink'
where mem_id = 'BLK';

Error Code: 1451. Cannot delete or update a parent row: 
a foreign key constraint fails (`naver_db`.`buy`, CONSTRAINT `buy_ibfk_1` FOREIGN KEY (`mem_id`) REFERENCES `member` (`mem_id`))

 

 

• ON UPDATE/DELETE CASCADE 사용
  • ON UPDATE CASCADE : 기준 테이블의 열 이름이 변경되면 참조 테이블의 데이터도 변경되는 기능
  • ON DELETE CASCADE : 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 기능

 

drop table if exists 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;**

-- member 테이블의 BLK를 PINK로 변경
update member set mem_id = 'PINK'
where mem_id = 'BLK';

-- 다시 내부조인 후 정보 확인
select M.mem_id, M.mem_name, B.prod_name
	from buy B
		inner join member M
			on B.mem_id = M.mem_id;

 

3. UNIQUE 제약조건

• 중복되지 않는 유일한 값을 입력
• 기본 키(PK)와 비슷하지만 고유 키는 NULL 값을 허용함
• 고유 키로 여러 열을 선택해도 됨
• 고유 키로 설정할 열을 NOT NULL로 지정하면 고유 키도 기본 키와 동일하게 중복도 안 되고 비어있어도 안 됨

 

drop table if exists buy, member;

create table member
( mem_id   char(8)     not null primary key,
  mem_name varchar(10) not null,
  height   tinyint unsigned null,
  **email    char(8)     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');

Error Code: 1062. Duplicate entry 'pink@gmail.com' for key 'member.email

 

▶ 고유 키(Unique)는 중복을 허용하지 않음 / 비어 있는 값, Null 값만 허용

 

 

4. CHECK 제약조건

• 입력되는 데이터를 점검하는 기능

 

4.1 CREATE TABLE 에서 설정하는 CHECK 제약조건

• 열의 정의 뒤에 CHECK(조건) 을 추가
  • 평균 키는 반드시 100 이상의 값만 입력되도록 설정

drop table if exists member;

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

Error Code: 3819. **Check constraint** 'member_chk_1' is violated.

 

▶ 체크 제약조건에서 설정한 값의 범위를 벗어났기에 오류 발생 (Check constraint 오류)

 

4.2 ALTER TABLE 에서 설정하는 CHECK 제약조건

-- 괄호 안에 있는 값 중 하나와 같아야 참(TRUE)
alter table member
	add constraint
    check (phone1 IN('02', '031', '032', '054', '055', '061'));

insert into member values('TWC', '트와이스', 167, '02');
insert into member values('OMY', '오마이걸', 167, '010');

Error Code: 3819. Check constraint 'member_chk_2' is violated.

 

▶ 체크 제약조건을 설정한 후 조건에 위배되는 값을 입력하면 오류 발생

 

5. DEFAULT 정의

• 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 저장해 놓는 방법

 

5.1 CREATE TABLE 에서 설정하는 DEFAULT 정의

drop table if exists member;

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

 

5.2 ALTER TABLE 에서 설정하는 DEFAULT 정의

alter table member
	alter column phone1 set default '02';

insert into member values('RED', '레드벨벳', 161, '054');
insert into member values('SPC', '우주소녀', default, default);

select * from member;

 

 

6. NULL 값 허용

• PK가 설정된 열에서 null 값 조건을 생략하면 자동으로 NOT NULL로 인식
• NULL : ‘아무것도 없다’라는 의미 + 공백(’ ‘)과 0과는 다름!!