멈추지 않는 기록

[혼자공부하는SQL] 13강 본문

웹 개발/MySQL

[혼자공부하는SQL] 13강

pangil_kim 2025. 2. 19. 14:44
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
'웹 개발/MySQL' Related Articles more