728x90

05-2. 제약 조건으로 테이블을 견고하게

0. 시작하기 전에

테이블을 만들 때는 테이블의 구조에 필요한 제약조건을 설정해줘야 한다.
이를 잘 활용하면 데이터의 오류를 줄여 완전무결한 코드를 만들 수 있다.
종류 : 기본 키(Primary Key), 외래 키(Foreign Key), 고유 키 (Unique), 체크(Check), 기본값(Default), NOT NULL
중복되지 않은 열에 고유 키를 지정할 수 있다.

1. 제약조건의 기본 개념과 종류

제약 조건은 데이터의 무결성을 지키기 위한 조건이다.
- 데이터의 무결성 : 데이터에 결함이 없음
이러한 결함을 방지하기 위해서 기본키를 지정할 수 있다.
기본키의 조건 : 중복되지 않고, 비어 있지도 않음
대표적인 제약 조건
- 기본 키(Primary Key)
- 외래 키(Foreign Key)
- 고유 키 (Unique)
- 체크(Check)
- 기본값(Default) 정의
- NOT NULL

2. 기본 키 제약조건

기본 키 : 데이터를 구분할 수 있는 식별자
예 : 회원 테이블의 아이디, 학생 테이블의 학번, 직원 테이블의 사번
특징
- 기본 키에 입력도니 값은 중복될 수 없으며, NULL 값이 입력될 수 없다.
- 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.
- 테이블은 기본 키를 1개만 가질 수 있다.
기본 키가 없어도 테이블 구성이 가능하지만, 실무에 사용하는 테이블에는 기본 키를 설정해야, 중복된 데이터가 입력되지 않는다.

1) CREATE TBALE에서 설정하는 기본 키 제약조건

(1) 열 자체에 지정

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

(2) 열 밖에서 지정

DROP TABLE IF EXISTS member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL,
    PRIMARY KEY (mem_id)
);

(3) 테이블 정보를 수정하여 지정

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 -- 테이블 정보를 수정하겠다
    ADD CONSTRAINT -- 제약 조건을 추가하겠다
        PRIMARY KEY (mem_id); -- 이 조건을

2) CONSTRAIN

내용단순히 PK의 이름을 지정하는 것뿐만 아니라, 나중에 해당 제약 조건을 쉽게 참조하거나 변경할 수 있도록 하기 위해서도 사용된다.
1. 명확한 제약 조건 관리: 기본 키뿐만 아니라 UNIQUE, CHECK, FOREIGN KEY 등 여러 제약 조건을 정의할 때 이름을 부여하면 쉽게 관리할 수 있다.
2. 제약 조건 삭제 및 수정 용이: 특정 제약 조건을 삭제하거나 변경할 때 이름이 있으면 더 편리하다.
3. ALTER TABLE member DROP CONSTRAINT PK_member_mem_id;
4. 가독성 향상: 여러 개의 제약 조건이 존재하는 경우, 명확한 이름을 부여하면 코드의 가독성이 좋아진다.
예제 (CONSTRAINT 사용 vs 미사용)
```
-- CONSTRAINT로 기본 키에 이름 지정
CREATE TABLE member (
    mem_id CHAR(8) NOT NULL,
    mem_name VARCHAR(10) NOT NULL,
    CONSTRAINT PK_member_mem_id PRIMARY KEY (mem_id)
);
```
이처럼 CONSTRAINT를 사용하면 PK의 이름을 직접 지정할 수 있고, 나중에 관리하기도 편하다.
-- CONSTRAINT 없이 기본 키 지정 CREATE TABLE member ( mem_id CHAR(8) NOT NULL PRIMARY KEY, mem_name VARCHAR(10) NOT NULL );
CONSTRAINT를 사용하는 이유
CONSTRAINT는 제약 조건(Constraint)에 이름을 부여하고 관리하기 위해 사용된다.

3. 외래 키 제약조건

외래 키 제약 조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 한다.
왜리 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결된다.
기본 키가 있는 테이블을 ‘기준 테이블’이라 부르고, 외래 키가 있는 테이블을 ‘참조 테이블’이라고 부른다.
참조 테이블이 참조하는 기준 테이블은 반드시 기본 키(Primary Key)나 고유키(Unique)로 걸정되어 있어야 한다.

1) CREAT TABLE에서 설정하는 외래 키 제약 조건

CREATE TABLE 끝에 FOREGIEN KEY를 설정

(1) 열 자체에 지정

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

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

-- 외래 키의 이름도 바꿀 수 있다. (히지만 혼란을 위해 동일한 이름을 권장한다.)
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
);

CREATE TABLE buy
(
    num       INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    user_id   CHAR(8)            NOT NULL,
    prod_name CHAR(6)            NOT NULL,
    FOREIGN KEY (user_id) REFERENCES member (mem_id)
);

(2) 테이블 정보를 수정하여 지정

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

2) 기준 테이블의 열이 변경될 경우

(1) 문제가 되는 상황

INSERT INTO member VALUES ('BLK', '블랙핑크', 163);
INSERT INTO buy VALUES (NULL, 'BLK', '지갑');
INSERT INTO buy VALUES (NULL, '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;

-- PK와 FK로 연결되어 있기에, 변경/삭제가 불가능하다. (데이터 무결성을 보장하기 위해서)
UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';
DELETE FROM member WHERE mem_id = 'BLK';

(2) 해결 방법

기준 테이블의 PK의 내용이 바뀌게 되면, 참조 테이블의 FK도 다 변경되게끔 한다.
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', '맥북');

-- 검색 (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;

-- PK 수정
UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';
-- 검색 (PINK로 바뀐 상태)
SELECT M.mem_id, M.mem_name, B.prod_name
    FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id;

-- PK 삭제
DELETE FROM member WHERE mem_id = 'PINK';
-- 검색
SELECT * FROm buy;

4. 기타 제약조건

1) 고유 키 제약 조건

고유 키(Unique) 제약 조건은 ‘중복되지 않는 유일한 값’을 입력해야 하는 조건이다.
기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약조건은 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(30)         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');

2) 체크 제약조건

체크 제약조건은 입력되는 데이터를 점검하는 기능을 한다.
예 : 평균 키(height)에 마이너스 값이 입력되지 않도록 하거나, 연락처 국번에 03, 031, 041, 055 중 하나만 입력되도록 할 수 있다.

(1) 값의 범위

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 CHECK ( height >= 100 ),
    phone1   CHAR(3)          NULl
);

-- 정상
INSERT INTO member VALUES ('BLK', '블랙핑크', 163, NULL);
-- 오류
INSERT INTO member VALUES ('TWC', '트와이스', 99, NULL);

(2) 일정 범위 중 일부만 입력

-- 연락처(phone1의 범위 지정)
ALTER TABLE member
    ADD CONSTRAINT
    CHECK (phone1 IN ('02', '031', '032', '054', '055', '061'));

-- 정상
INSERT INTO member VALUES ('TWC', '트와이스', 163, '02');
-- 오류
INSERT INTO member VALUES ('OMY', '오마이걸', 99, '010');

3) 기본값 정의

기본값(Default) 정의는 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.
예 : 키를 입력하지 않고 기본적으로 160이라고 입력되도록 하고 싶은 경우

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

-- CONSTAINT와 문법이 조금 다르다.
ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';

-- 정상
INSERT INTO member VALUES ('RED', '레드벨벳', 163, '054');
-- 오류
INSERT INTO member VALUES ('SPC', '우주소녀', default, default);

SELECT * FROM member;

INSERT 입력 시, VALUES에서 default 키워드를 사용하여 기본값을 사용한다.

4) 널 값 허용

널(NULl) 값을 허용하려면 생략하거나 NULL을 사용하고, 허용하지 않으려면 NOT NULL을 사용한다.
다만 PRIMARY KEY가 설정된 열에 NULl 값이 잇을 수 없으므로, 생략하면 자동으로 NOT NULL로 ㅇ니식된다.
NULl 값은 ‘아무 것도 없다’라는 의미이며, 공백(’’)이나 0과는 다르다.

728x90

'💻 개발 > 📀 MySQL' 카테고리의 다른 글

[혼자공부하는SQL] 15강 (0)	2025.02.26
[혼자공부하는SQL] 14강 (0)	2025.02.19
[혼자공부하는SQL] 12강 (0)	2025.02.19
[혼자공부하는SQL] 11강 (0)	2025.02.14
[혼자공부하는SQL] 10강 (0)	2025.02.13

[혼자공부하는SQL] 13강

05-2. 제약 조건으로 테이블을 견고하게

0. 시작하기 전에

1. 제약조건의 기본 개념과 종류

2. 기본 키 제약조건

1) CREATE TBALE에서 설정하는 기본 키 제약조건

2) CONSTRAIN

3. 외래 키 제약조건

1) CREAT TABLE에서 설정하는 외래 키 제약 조건

2) 기준 테이블의 열이 변경될 경우

4. 기타 제약조건

1) 고유 키 제약 조건

2) 체크 제약조건

3) 기본값 정의

4) 널 값 허용

'💻 개발 > 📀 MySQL' 카테고리의 다른 글

티스토리툴바