0. 학습 목표
- 테이블의 구조에 대해 완벽하게 이해
- 테이블의 핵심인 제약조건을 학습하고 적절하게 지정
- 뷰의 개념과 실제 작동하는 방법
0. 요약
- CREATE TABLE은 테이블을 생성하는 SQL로 테이블 이름, 열, 데이터 형식 등을 지정
- 열에 입력될 값이 1부터 자동 증가하도록 설정하려며 GUI에서는 AI를 체크하고 SQL에서는 AUTO_INCREMENT를 입력
- 열에 빈 값을 허용하지 않으려면 GUI에서는 NN, SQL에서는 NOT NULL
- 열을 기본 키로 지정하려면 GUI에서는 PK, SQL에서는 PRIMARY KEY
- 열을 외래 키로 지정하려면 FOREIGN KEY 예약어를 입력
- 기본 키는 행 데이터 중에서 데이터를 구분할 수 있는 식별자 역할을 함
- 외래 키는 두 테이블의 관계를 연결, 외래 키가 설정된 열은 꼭 다른 테이블의 기본키와 연결됨
- 고유 키는 중복되지 않은 유일한 값, 기본 키와 차이점은 NULL을 허용
- 체크는 입력되는 데이터를 점검하는 기능
- 기본값은 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정
- NULL값을 허용하려면 생략하거나 NULL을 사용, 허용하지 않으려면 NOT NULL을 사용
| 용어 | 설명 |
| 제약조건 | 데이터의 무결성을 지키기 위한 제한된 조건 |
| ALTER TABLE | 이미 만들어진 테이블을 수정하는 SQL문 |
| ADD CONSTRAINT | 제약조건을 추가하는 SQL문 |
| 기준 테이블 | 기본 키-외래 키 관계에서 기본 키가 설정된 테이블 |
| 참조 테이블 | 기본 키-외래 키 관계에서 외래 키가 설정된 테이블 |
| ON UPDATE CASCADE | 기준 테이블의 기본 키를 변경하면 참조 테이블의 외래 키도 변경되는 기능 |
| ON DELETE CASCADE | 기준 테이블의 기본 키를 삭제하면 참조 테이블의 외래 키도 삭제되는 기능 |
2. 제약조건으로 테이블을 견고하게
※핵심 키워드: 기본 키, 외래 키, 고유 키, 체크, 기본값, NULL
테이블을 만들 때는 테이블 구조에 필요한 제약조건을 설정하여 데이터의 오류가 적고 견고한 테이블을 만들 수 있다.
- 기본 키(Primary): 아이디, 사번, 학번 등과 같은 고유한 번호
- 외래 키(Foregin): 기본 키와 연결되는 열에 지정
- 고유 키(Unique): 휴대폰, 이메일 번호와 같이 고유한 값에 지정
- 체크(check): 여자 아이돌의 평균 키가 200cm를 넘지 않을 것을 고려하여 제약조건인 체크 설정
- 기본(default): 국적의 기본 값을 대한민국으로 설정
2-1. 제약조건의 기본 개념과 종류
제약조건(constrint)은 데이터의 무결성을 지키기 위해 제한하는 조건이다. 예를 들어 회원의 ID가 중복된다면 혼란이 발생한다. 이런 결함이 없는 것을 데이터의 무결성이라고 한다.
이러한 결함을 미리 방지하기 위해 회원 테이블의 아이디를 기본키로 지정할 수 있다. MySQL에서 제공하는 대표적인 제약조건은 아래와 같다.
- PRIMARY KEY 제약조건
- FOREIGN KEY 제약조건
- UNIQUE 제약조건
- CHECK 제약조건
- DEFAULT 정의
- NULL 값 허용
2-2. 기본 키 제약조건
테이블에는 많은 데이터가 있지만 데이터를 구분할 수 있는 식별자를 기본 키(PRIMARY KEY)라고 한다.
기본 키에 입력되는 값은 중복될 수 없으며 NULL값이 입력될 수 없다.
또한 테이블은 기본키를 꼭 가질 필요는 없지만 기본 키를 설정해야 중복된 데이터가 입력되지 않는다. 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 된다.
마지막으로 키본키는 테이블당 1개만 가질 수 있다.
CREATE TABLE에서 설정하는 기본 키 제약조건
기본 키를 생성하는 방법은 열 이름 뒤에 PRIMARY KEY를 넣어주거나, 제일 마지막 행에 PRIMARY KEY (mem_id)를 추가하는 것이다.
USE naver_db;
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
......
);
CREATE TABLE member
( mem_id CHAR(8) NOT NULL,
......
PRIMARY KEY (mem_id)
);
※만약 기본키-외래키로 연결되어 있다면 당연히 외래 키의 테이블을 먼제 삭제 후 기본키 테이블이 삭제가능하다.
DESCRIBE 문을 사용하여 테이블의 정보를 확인해 보자. mem_id가 PRIMARY KEY로 지정되어 있음을 확인할 수 있다.
DESCRIBE member;
# Field, Type, Null, Key, Default, Extra
'mem_id', 'char(8)', 'NO', 'PRI', NULL, ''
'mem_name', 'varchar(10)', 'NO', '', NULL, ''
'mem_number', 'tinyint', 'NO', '', NULL, ''
'addr', 'char(2)', 'NO', '', NULL, ''
'phone1', 'char(3)', 'YES', '', NULL, ''
'phone2', 'char(8)', 'YES', '', NULL, ''
'height', 'tinyint unsigned', 'YES', '', NULL, ''
'debut_date', 'date', 'YES', '', NULL, ''ALTER TABLE에서 설정하는 기본 키 제약조건
이미 테이블이 만들어진 상태에서 기본 키를 설정하는 경우 테이블을 수정하는 ALTER TABLE 구문을 사용하여 변경 가능하다.
ALTER TABLE member
ADD CONSTRAINT
PRIMARY KEY (mem_id);참고로 기본 키도 별도의 이름을 만들 수 있다.
CONSTRAINT PRIMARY KEY PK_member_mem_id (mem_id);2-3. 외래 키 제약조건
외래 키 제약조건은 두 테이블 사이의 관계를 연결해주고 그 결과 데이터의 무결성을 보장해 주는 역할을 한다. 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결된다.
기본 키가 있는 테이블이 기준 테이블이며 외래 키가 있는 테이블이 참조 테이블 이다.
외래 키 제약조건은 기준 테이블에 없는 정보가 참조 테이블에는 없도록 하는 것이다.
예제로 지난 구매테이블에서 회원테이블에 없는 정보가 있어 입력되지 않은 것을 기억할 것이다.
한 가지 주의할 사항은 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나 고유 키로 설정되어 있어야 한다.
CREATE TABLE에서 설정하는 외래 키 제약조건
외래 키를 생성하는 방법은 CREATE TABLE 끝에 FOREIGN KEY 키워드를 설정하는 것이다. 참고로 기준 테이블과 참조 테이블의 열 이름이 꼭 같지 않아도 상관 없다.
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
.......
);
CREATE TABLE buy
( num INT AUTO INCREMENT NOT NULL PRIMARY KEY,
mem_id CHAR(8) NOT NULL,
.......,
FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);ALTER TABLE에서 설정하는 외래 키 제약조건
이미 테이블이 만들어진 상태에서 외래 키를 설정하는 경우 테이블을 수정하는 ALTER TABLE 구문을 사용하여 변경 가능하다.
ALTER TABLE buy
ADD CONSTRAINT
FOREIGN KEY(mem_id)
REFERENCES member(mem_id);기준 테이블의 열이 변경될 경우
기준 테이블의 열이 변경되는 경우는 어떻게 될까 예를 들어 기준 테이블의 mem_id 중 BLK를 PINK로 변경해 보자.
member와 buy테이블에서 BLK가 어떻게 되어 있는지 확인하자.
SELECT * FROM member WHERE member.mem_id = 'BLK';
# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
SELECT * FROM buy WHERE buy.mem_id = 'BLK';
# num, mem_id, prod_name, group_name, price, amount
'1', 'BLK', '지갑', NULL, '30', '2'
'2', 'BLK', '맥북프로', '디지털', '1000', '1'내부 조인을 사용해서 물품 정보 및 사용자 정보를 확인해 보자.
SELECT M.mem_id, M.mem_name, B.prod_name
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
# mem_id, mem_name, prod_name
'BLK', '블랙핑크', '지갑'
'BLK', '블랙핑크', '맥북프로'
자 이제 BLK를 PINK로 변경해 보자. 변경이 되지 않고 에러가 발생한다. 이유는 기본키-외래키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않는다.
삭제도 당연히 되지 않는다.
UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';
14:01:56 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`)) 0.031 sec
DELETE FROM member WHERE mem_id='BLK';
14:02:48 DELETE FROM member 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`)) 0.016 sec이런 경우 기준 테이블인 member 테이블의 열 이름이 변경되면 자동으로 참조 테이블인 buy 테이블이 변경된다면 효율 적일 것이다.
이런 기능을 지원하는 것이 업데이트는 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;
INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');자 이제 member table에서 mem_id가 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;
# mem_id, mem_name, prod_name
'PINK', '블랙핑크', '지갑'
'PINK', '블랙핑크', '맥북'이번에는 member 테이블에서 mem_id가 PINK인 것을 삭제해 보자. buy 테이블에서도 삭제된 것을 확인할 수 있다.
DELETE FROM member WHERE mem_id = 'PINK';
SELECT * FROM buy;
# num, mem_id, prod_name2-4. 기타 제약 조건
위 핵심 제약 조건 외 필요한 제약 조건에 대해 확인하자.
고유 키 제약조건
고유 키(Unique) 제약 조건은 중복되지 않는 유일한 값을 입력해야 하는 조건이다. 기본 키와 비슷하지만 차이점도 있다.
- 고유 키는 중복되지 않는 유일한 값을 입력해야 한다.(기본 키와 동일)
- 고유 키는 NULL 값을 허용하며 여러 개가 입력되어도 된다.(기본 키와 차이)
- 고유 키는 테이블에서 여러개 설정이 가능하다(기본 키와 차이)
예를 들어 member 테이블에 Email 주소가 있다면 중복되지 않으므로 공유 키로 설정할 수 있다.
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,
email CHAR(30) NULL UNIQUE
);email에 중복된 값을 입력해 보자. 오류가 발생한다.
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');
15:51:11 INSERT INTO member VALUES('APN', '에이핑크', 164, 'pink@gmail.com') Error Code: 1062. Duplicate entry 'pink@gmail.com' for key 'member.email' 0.000 sec체크 제약조건
체크 제약조건은 입력되는 데이터를 점검하는 기능을 한다. 예를 들어 평균 키인 height가 100cm이상, 연락처 국번 phone1이 02, 031, 041, 055 중 하나만 입력되도록 할 수 있다.
평균 키가 100이상인 값만 입력되도록 체크 설정을 해보자. 열의 정의 뒤 CHECK (조건)을 추가해 주면 된다.
USE naver_db;
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
);height를 100이하로 추가해 보자. 오류가 발생한다.
INSERT INTO member VALUES('BLK', '블랙핑크', 163, NULL);
INSERT INTO member VALUES('TWC', '트와이스', 99, NULL);
15:57:12 INSERT INTO member VALUES('TWC', '트와이스', 99, NULL) Error Code: 3819. Check constraint 'member_chk_1' is violated. 0.000 sec필요하다면 테이블을 만든 후 ALTER TABLE 문으로 제약조건을 추가할 수 있다. phone1에 02, 031, 032, 054, 055, 061 중 하나만 입력되도록 설정해 보자.
ALTER TABLE member
ADD CONSTRAINT
CHECK (phone1 IN('02', '031', '032', '054', '055', '061'));phone1에 010을 입력해 보자. 오류가 발생한다.
INSERT INTO member VALUES('TWC', '트와이스', 167, '02');
INSERT INTO member VALUES('OMY', '오마이걸', 167, '010');
16:06:03 INSERT INTO member VALUES('OMY', '오마이걸', 167, '010') Error Code: 3819. Check constraint 'member_chk_2' is violated. 0.000 sec기본값 정의
기본 값(Default) 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓은 방법이다. 예를 들어 평균 키height를 입력하지 않고 기본적으로 160이라고 입력되도록 하고 싶다면 다음과 같이 정의할 수 있다.
USE naver_db;
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
);필요하다면 테이블을 만든 후 ALTER TABLE 문으로 DEFAULT를 지정할 수 잇다. ALTER COLUMN문으로 phone1에 아무것도 입력하지 않으면 자동으로 02가 입력되도록 해보자.
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;
# mem_id, mem_name, height, phone1
'RED', '레드벨벳', '161', '054'
'SPC', '우주소녀', '160', '02'널 값 허용
널 값을 허용하려면 생략하거나 NULL을 사용하고 허용하지 않으려면 NOT NULL을 사용한다.
주의할 점은 NULL은 아무것도 없다는 의미이다. 공백과는 다르다는 것에 주의하여야 한다.
'혼자공부하는 SQL' 카테고리의 다른 글
| 6. 인덱스-1 (0) | 2022.02.09 |
|---|---|
| 5. 테이블과 뷰-3 (0) | 2022.02.08 |
| 5. 테이블과 뷰-1 (0) | 2022.02.03 |
| 4. SQL 고급 문법-3 (0) | 2022.01.29 |
| 4. SQL 고급 문법-2 (0) | 2022.01.28 |
