[ SQL 스터디 - 3주차 ] 테이블
위 포스트는 "혼자 공부하는 SQL" 책 Chapter 05-1, 05-2 내용을 정리한 글입니다.
한빛미디어 - 혼자 공부하는 SQL (우재남 저) : 무료 동영상 강의 제공
누구를 위한 책인가요? SQL을 처음 시작하려고 하는 학생과 취업 준비생, 데이터베이스 기초를 배우고자 하는 초보 개발자, SQL의 이론과 실습을 동시에 학습하고 싶은 입문자, SQL을 공부하다가
hongong.hanbit.co.kr
Chapter 05-1 . 테이블 만들기
** 데이터베이스와 테이블 설계하기
1. 데이터베이스 생성하기
2. 테이블 생성하기
3. 데이터 입력하기
Chapter 05-2 . 제약조건으로 테이블을 견고하게
** 제약조건의 기본 개념과 종류
제약조건이란
데이터의 무결성을 지키기 위해 제한하는 조건이다.
** 데이터 무결성
'데이터에 결함이 없음' 이라는 의미
데이터 중복이 없는 것도 결함이 없다는 말과 같음
이 무결성, 즉 결함을 미리 방지하기 위해서
MySQL에서는 아래와 같은 제약조건을 가지고 있다.
* PRIMARY KEY 제약조건
* FOREIGN KEY 제약조건
* UNIQUE 제약조건
* CHECK 제약조건
* DEFAULT 정의
* NULL 값 허용
각각의 제약조건에 대해 하나씩 알아보자.
** 기본 키 제약조건
기본 키란
테이블을 구성하는 데이터를 구분할 수 있는 식별자를 의미한다.
기본키는
- 입력되는 값은 중복될 수 x // (ex) 회원가입 시 회원 아이디 중복 x
- NULL 값이 입력될 수 x // (ex) 회원 아이디 없이 회원가입 x
위와 특징을 가지고 있으며,
테이블의 특성을 가장 잘 반영하는 하나의 열에 설정되어야 한다.
[ 1주차 ] 데이터 모델링의 이해
위 포스트는 "혼자 공부하는 SQL" 책 Chapter 2-1 내용과 "유선배 SQL개발자(SQLD) 과외노트" 책 Part 1-1 내용을 바탕으로 정리한 글입니다. 한빛미디어 - 혼자 공부하는 SQL (우재남 저) : 무료 동영상 강의
yangheeb.tistory.com
기본 키 제약조건에 대해서는 이전 포스트에서 자세히 설명해두었으니 참고바란다.
01. CREATE TABLE에서 설정하는 기본 키 제약조건
기본 키 제약조건을 사용하는 방식으로 2가지가 존재한다.
우선 CREATE TABLE문에서 PRIMARY KEY 예약어를 넣어 기본 키 제약조건을 부여하는 법을 알아보자.
USE naver_db;
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
);
DESCRIBE member;
아래와 같이 열 이름 뒤에 PRIMARY KEY를 붙여주면서
mem_id를 member 테이블의 기본키로 설정할 수 있다.
mem_id CHAR(8) NOT NULL PRIMARY KEY,
뿐만 아니라 아래와 같이
테이블의 제일 마지막에 PRIMARY KEY(열_이름)을 붙여주면, 그 열이 기본 키로 설정된다.
DROP TABLE IF EXISTS buy,member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
PRIMARY KEY (mem_id)
);
02. ALTER TABLE에서 설정하는 기본 키 제약조건
앞서 CREATE TABLE 문에서 PRIMARY KEY 예약어를 넣어 기본 키 제약조건을 부여하는 법을 알아보았다.
제약조건을 설정하는 또 다른 방법으로,
이미 만들어진 테이블을 수정하는 ALTER TABLE 구문 사용하여 기본키를 지정하는 법을 알아보자.
DROP TABLE IF EXISTS buy,member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
);
ALTER TABLE member
ADD CONSTRAINT
PRIMARY KEY (mem_id);
DESCRIBE member;
CREATE TABLE 안에서 PRIMARY KEY를 설정한 것과 동일한 결과를 확인할 수 있다.
* ALTER TABLE member -> member를 변경
* ADD CONSTRAINT -> 제약조건을 추가
* PRIMARY KEY (mem_id); -> mem_id 열에 기본 키 제약조건을 설정
** 외래 키 제약조건
외래 키 제약조건이란
두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 한다.
우리가 자주 사용하는 테이블의 관계를 '기본 키 - 외래 키'
즉 PK-FK 관계라고 하는데,
이 기본 키가 있는 테이블을 '기준 테이블', 외래 키가 있는 테이블을 '참조 테이블'이라고 부른다.
참조 테이블이 참조하는 기준 테이블의 열은
반드시 !! 기본 키나, 고유 키로 설정되어 있어야 한다.
01. CREATE TABLE에서 설정하는 외래 키 제약조건
외래 키를 생성하는 방법 역시 기본 키 설정 방식과 동일하다.
둘다 CREATE TABLE, ALTER TABLE에서 키 제약조건을 설정한다.
먼저 CREATE TABLE에서 설정하는 외래 키 제약조건 방식을 알아보자.
CREATE TABLE 끝에 FOREIGN KEY 키워드를 설정한다.
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
);
DESCRIBE member;
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) PREFERENCES member(mem_id)
);
마지막 줄 코드와 같이
FOREIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름) 와 같은 형식으로 외래 키를 정의할 수 있다.
02. ALTER TABLE에서 설정하는 외래 키 제약조건
또 다른 방법인
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,
FOREIGN KEY(mem_id) PREFERENCES member(mem_id)
);
ALTER TABLE buy
ADD CONSTRAINT
FOREIGN KEY(mem_id)
REFERENCES member(mem_id);
* ALTER TABLE buy -> buy를 수정
* ADD CONSTRAINT -> 제약조건을 추가
* FOREIGN KEY(mem_id) -> 외래 키 제약조건을 buy 테이블의 mem_id에 설정
* REFERENCES member(mem_id) -> 참조할 기준 테이블은 member 테이블의 mem_id열
위와 같이 ALTER TABLE 문을 작성하여
외래 키 제약조건을 부여할 수 있다.
03. 기준 테이블의 열이 변경될 경우
기본 키 - 외래 키. 즉 PK - FK로 맺어진 후에는 이미 테이블끼리 조인을 했기 때문에,
PK를 가지고 있는 테이블인 “기준 테이블”의 열 이름이 변경되거나 삭제되지 않는다.
이는 열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문이다.
이 문제를 해결하기 위해 사용되는 기능이
ON UPDATE CASCADE문 과 ON DELETE CASCADE문이다.
테이블을 생성할 때 아래와 같이 ALTER TABLE문에
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,
FOREIGN KEY(mem_id) PREFERENCES member(mem_id)
);
ALTER TABLE buy
ADD CONSTRAINT
FOREIGN KEY(mem_id) REFERENCES member(mem_id)
ON UPDATE CASCADE
ON DELETE CASCADE;
* ON UPDATE CASCADE -> 기준 테이블의 데이터가 변경되면 참조 테이블의 데이터도 자동으로 변경되는 기능
* ON DELETE CASCADE -> 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 자동으로 삭제되는 기능
ON UPDATE CASCADE문과 ON DELETE CASCADE문을 사용하여
새로 생성한 buy 테이블에 데이터를 입력한 후에
INSERT INTO buy VALUES (NULL, 'BLK', '지갑');
INSERT INTO buy VALUES (NULL, 'BLK', '맥북');
데이터를 수정하거나 삭제하면
UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';DELETE FROM member WHERE mem_id = 'BLK';
오류가 발생하지 않고 데이터가 변경된다.
다만 !! ON UPDATE CASCADE문과 ON DELETE CASCADE문을 사용을 하지 않고
위와 같이 데이터를 수정을 하려고 하거나 데이터를 삭제하는 쿼리를 입력한다면,
오류가 발생하는 것을 확인할 수 있다.
** 기타 제약조건
01. 고유 키 제약 조건
고유 키 제약조건이란
'중복되지 않은 유일한 값' 을 입력해야 하는 조건이다.
기본 키 제약조건과 거의 비슷하지만,
- NULL 값을 허용한다는 점
- NULL 값이 여러 개가 입력되어도 상관없다는 점
- 기본키는 테이블 당 1개만 설정해야 하지만, 고유 키는 여러 개 설정해도 된다는 점
에서 기본 키와 차이가 있다.
고유 키(Unique)는 중복은 허용하지 않지만, 비어 있는 값은 허용하기에
아래의 코드와 같이 NULL UNIQUE로 표현하여 CREATE TABLE에서 테이블을 생성할 수 있다.
email CHAR(30) NULL UNIQUE,
INSERT INTO ~ VALUES에서도 마찬가지로 고유키는 NULL이 허용되지만,
아래와 같이 이메일 값이 중복된다면 오류가 발생한다.
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');
02. 체크 제약조건
체크 제약조건이란
입력되는 데이터를 점검하는 기능을 하는 조건이다.
CREATE TABLE을 통해서 테이블을 생성할 때
아래와 같이 열의 정의 뒤에 CHECK 제약조건이 설정되고,
height TINYINT UNSIGNED NULL CHECK (height>=100),
위에서 설정한 CHECK 제약조건으로 인해
제약조건에 위배되는 범위의 데이터가 입력될 시 오류가 발생한다.
03. 기본값 정의
기본값 (Default) 정의란
값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.
CREATE TABLE 문에서는 아래와 같이 "DEFAULT 00"로 기본값 정의를 하고,
height TINYINT UNSIGNED NULL DEFAULT 160,
ALTER TABLE에서는 ALTER COLUMN문을 사용하여 기본값을 정의하며,
ALTER TABLE member
ALTER COLUMN phone1 SET DEFAULT '02';
기본값이 설정된 열에서는 default 라고 써주고, 기본값을 입력할 수 있다.
INSERT INTO member VALUES('RED','레드벨벳',161,'054');
INSERT INTO member VALUES('SPC','우주소녀',default,default);
SELECT * FROM member;
위와 같이 기본값을 설정한 후에 default로 지정하면,
설정된 기본값이 입력되는 것을 확인할 수 있다.
04. 널 값 허용
NULL은 빈 값을 허용, NOT NULL은 빈 값을 허용하지 않는다는 의미로 사용된다.
다만 PRIMARY KEY가 설정된 열에는 NULL값이 있을 수 없기 때문에,
이것을 생략한다면 자동으로 NOT NULL로 인식된다.