20260506WED (DAY 27)

sungsimdangmascot·2026년 5월 6일
DBMS

DBMS

1. 제약 조건 (Constraint)

제약 조건이란 데이터베이스 테이블에 저장되는 데이터에 규칙을 거는 것이다. 마치 학교에서 "교복을 입어야 한다", "지각하면 안 된다"처럼 규칙을 정해놓는 것과 같다.

1) PK (Primary Key, 기본키)

PK는 테이블의 각 행(row)을 구별하는 유일한 식별자다.

  • 중복된 값이 있을 수 없다.
  • NULL(빈 값)이 될 수 없다.

쉽게 말하면, 학교 학생증의 학번과 같다. 학번은 모든 학생이 다르고, 학번이 없는 학생도 없다.

CREATE TABLE student (
    student_id INT PRIMARY KEY,  -- 학번이 PK
    name VARCHAR(50),
    age INT
);

-- PK이기 때문에 같은 student_id 두 번 넣으면 오류 발생
INSERT INTO student VALUES (1001, '홍길동', 15);
INSERT INTO student VALUES (1001, '김철수', 16);  -- 오류! 중복 불가
INSERT INTO student VALUES (NULL, '이유리', 14);  -- 오류! NULL 불가

2) FK (Foreign Key, 외래키)

FK는 다른 테이블의 PK를 가져와서 연결할 때 사용하는 열(column)이다.

  • 중복이 가능하다.
  • NULL도 허용한다.
  • 두 테이블을 이어주는 다리 역할을 한다.

쉽게 말하면, 반 학생 명단에 담임 선생님 번호가 적혀 있는 것과 같다. 여러 학생이 같은 담임 선생님 번호를 가질 수 있다.

CREATE TABLE team (
    team_id VARCHAR(10) PRIMARY KEY,  -- 팀 고유 번호
    team_name VARCHAR(50)
);

CREATE TABLE player (
    player_id INT PRIMARY KEY,
    player_name VARCHAR(50),
    team_id VARCHAR(10),
    FOREIGN KEY (team_id) REFERENCES team(team_id)  -- team 테이블의 PK를 참조
);

-- team 테이블에 없는 팀 id를 player에 넣으면 오류!
INSERT INTO team VALUES ('K01', '울산현대');
INSERT INTO player VALUES (1, '손흥민', 'K01');   -- 정상
INSERT INTO player VALUES (2, '박지성', 'K99');   -- 오류! K99라는 팀이 없음

3) NOT NULL

특정 컬럼에 절대 빈 값(NULL)을 허용하지 않겠다는 제약이다.

이름이 없는 학생 정보를 저장하면 안 된다고 규칙을 정하는 것과 같다.

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,   -- 이름은 반드시 있어야 함
    phone VARCHAR(20)            -- 전화번호는 없어도 됨 (NULL 허용)
);

INSERT INTO student VALUES (1001, NULL, '010-1234-5678');  -- 오류! name은 NOT NULL
INSERT INTO student VALUES (1002, '홍길동', NULL);          -- 정상! phone은 NULL 허용

4) CHECK

특정 컬럼에 특정 값만 들어올 수 있도록 조건을 거는 것이다. MySQL 8버전 이상에서 사용할 수 있다.

나이를 저장하는 컬럼에 음수가 들어오면 이상하니까 "0보다 커야 한다"는 규칙을 걸어두는 것이다.

CREATE TABLE player (
    player_id INT PRIMARY KEY,
    player_name VARCHAR(50),
    height INT CHECK (height > 0 AND height < 300),   -- 키는 0~300 사이만 허용
    salary INT CHECK (salary >= 0)                     -- 급여는 0 이상만 허용
);

INSERT INTO player VALUES (1, '홍길동', 185, 5000);   -- 정상
INSERT INTO player VALUES (2, '김철수', -10, 3000);   -- 오류! 키는 0보다 커야 함
INSERT INTO player VALUES (3, '이유리', 170, -100);   -- 오류! 급여는 0 이상이어야 함

5) DEFAULT

값을 넣지 않았을 때 자동으로 들어갈 기본값을 미리 설정해두는 것이다.

회원가입할 때 포인트를 0으로 시작하게 하거나, 가입날짜를 오늘 날짜로 자동 저장하는 것이 DEFAULT의 예시다.

CREATE TABLE member (
    member_id INT PRIMARY KEY,
    name VARCHAR(50),
    point INT DEFAULT 0,                         -- 포인트 기본값은 0
    join_date DATE DEFAULT (CURRENT_DATE)        -- 가입일 기본값은 오늘 날짜
);

INSERT INTO member (member_id, name) VALUES (1, '홍길동');
-- point는 0, join_date는 오늘 날짜로 자동 저장됨

SELECT * FROM member;
-- 결과: 1 | 홍길동 | 0 | 2024-01-15

6) 복합키 (Composite Key)

두 개 이상의 컬럼을 묶어서 하나의 PK로 사용하는 것이다.

테이블에는 PK가 딱 1개만 존재할 수 있는데, 복합키를 쓰면 여러 컬럼을 하나의 PK처럼 쓸 수 있다.

수업 출석부를 예로 들면, "학번 + 수업명"을 함께 써야 어떤 학생이 어떤 수업에 출석했는지를 구분할 수 있다.

CREATE TABLE attendance (
    student_id INT,
    subject_name VARCHAR(50),
    score INT,
    PRIMARY KEY (student_id, subject_name)  -- 두 컬럼을 묶어서 복합키로 사용
);

INSERT INTO attendance VALUES (1001, '수학', 90);
INSERT INTO attendance VALUES (1001, '영어', 85);  -- 학번이 같아도 과목이 다르면 OK
INSERT INTO attendance VALUES (1002, '수학', 78);  -- 정상
INSERT INTO attendance VALUES (1001, '수학', 95);  -- 오류! student_id + subject_name 조합이 중복

2. 무결성 (Integrity)

무결성이란 데이터에 결함이 없는 완벽한 상태를 뜻한다. "무결(無缺)" = 결함이 없다는 한자 뜻이다.

쉽게 말해서, 데이터베이스에 저장된 데이터가 정확하고, 일관되고, 유효한 상태를 유지하는 것이다.

1) 무결성을 판단하는 3가지 기준

(1) 정확성 (Accuracy)

"데이터가 실제 사실을 그대로 반영하고 있는가?"

선수의 실제 키가 185cm인데 DB에는 오타로 158cm가 저장되어 있다면, 정확성이 깨진 것이다.

(2) 일관성 (Consistency)

"서로 다른 곳에 저장된 같은 데이터가 서로 모순되지 않는가?"

team 테이블에서는 K01팀이 '울산현대'인데, schedule 테이블에서는 K01이 '울산시티즌'으로 저장되어 있다면, 어떤 데이터가 맞는지 알 수 없으므로 일관성이 깨진 것이다.

(3) 유효성 (Validity)

"데이터가 미리 정해진 규칙과 범위 안에 있는가?"

급여 컬럼에 '0보다 커야 한다'는 규칙이 있는데 -5000이 저장되었다면, 유효성이 깨진 것이다.

2) 무결성의 3가지 종류

(1) 개체 무결성 (Entity Integrity)

  • "중복은 없다"
  • 기본키(PK)와 관련된 규칙이다.
  • 모든 테이블은 PK를 가져야 하고, PK는 NULL이거나 중복될 수 없다.
-- PK가 없으면 각 행을 구분할 수 없어서 개체 무결성이 깨질 수 있다
CREATE TABLE bad_example (
    name VARCHAR(50),   -- PK가 없어서 동명이인이 두 명이면 구분 불가
    age INT
);

-- 올바른 예시
CREATE TABLE good_example (
    id INT PRIMARY KEY,  -- PK가 있어야 개체 무결성 보장
    name VARCHAR(50),
    age INT
);

(2) 참조 무결성 (Referential Integrity)

  • "근거 없는 데이터는 없다"
  • 외래키(FK)와 관련된 규칙이다.
  • FK의 값은 참조하는 테이블의 PK에 실제로 존재하는 값이거나, NULL이어야 한다.
-- player 테이블의 team_id는 반드시 team 테이블에 존재하는 값이어야 한다
INSERT INTO team VALUES ('K01', '울산현대');

INSERT INTO player VALUES (1, '홍길동', 'K01');   -- 정상 (K01이 team 테이블에 존재)
INSERT INTO player VALUES (2, '김철수', 'K99');   -- 오류! K99는 team 테이블에 없음
-- => 참조 무결성 위반

(3) 도메인 무결성 (Domain Integrity)

  • "범위를 벗어나지 않는다"
  • 데이터 타입(자료형), CHECK, DEFAULT와 관련된 규칙이다.
  • 컬럼에 입력되는 값은 정의된 데이터 형식과 범위 안에 있어야 한다.
CREATE TABLE player (
    player_id INT PRIMARY KEY,
    name VARCHAR(50),
    height INT CHECK (height > 0)  -- 키 컬럼에는 숫자만, 0보다 큰 값만 허용
);

INSERT INTO player VALUES (1, '홍길동', '백팔십');  -- 오류! INT 컬럼에 문자 불가 (도메인 무결성 위반)
INSERT INTO player VALUES (2, '김철수', -10);        -- 오류! 0보다 작은 값 불가 (도메인 무결성 위반)

3. 정규화 (Normalization)

정규화란 데이터가 중복되거나 이상한 상황이 생기지 않도록 테이블을 올바르게 설계하는 작업이다.

정규화를 하지 않으면 아래 세 가지 이상현상이 발생한다.

이상현상이란?

다음 예시 테이블을 기준으로 설명한다.

학번(PK)학생이름전공과목명(PK)성적
1111홍길동컴공프로그래밍90
1111홍길동컴공운영체제89
2222김철수전자전자학77
3333이유리전자논리회로89
4444박웅이간호간호학개론100

(1) 삽입 이상 (Insertion Anomaly)

새로운 학생을 추가하고 싶은데 아직 수강 과목이 없다. 그런데 과목명이 PK의 일부이기 때문에 NULL로 넣을 수 없다. 어쩔 수 없이 '미정' 같은 불필요한 데이터를 억지로 넣어야 한다.

(2) 갱신 이상 (Update Anomaly)

홍길동이 전자과로 전과했다. 홍길동의 행이 2개이기 때문에 두 행을 모두 수정해야 한다. 만약 하나만 수정하면, 한 행은 '컴공', 다른 행은 '전자'로 데이터가 모순된다.

(3) 삭제 이상 (Deletion Anomaly)

박웅이가 자퇴하여 행을 삭제했다. 그런데 박웅이 행이 사라지면서 '간호학과'에 대한 정보도 함께 사라진다. 간호학과에 학생이 한 명도 없게 되어 버린다.

1) 1차 정규화

하나의 컬럼에는 하나의 값만 들어와야 한다.

같은 성격의 컬럼이 반복되거나, 하나의 칸에 여러 데이터가 들어가면 안 된다.

-- 잘못된 예시 1: 하나의 셀에 여러 값
-- 상품명
-- 바지1, 바지2, 바지3   <-- 하나의 셀에 여러 값이 들어가 있음

-- 잘못된 예시 2: 같은 성격의 컬럼 반복
CREATE TABLE bad_order (
    order_id INT PRIMARY KEY,
    item1 VARCHAR(50),   -- 상품1
    item2 VARCHAR(50),   -- 상품2
    item3 VARCHAR(50)    -- 상품3
);

-- 올바른 예시: 1차 정규화 후
CREATE TABLE order_item (
    order_id INT,
    item_name VARCHAR(50),
    PRIMARY KEY (order_id, item_name)
);

INSERT INTO order_item VALUES (1, '바지1');
INSERT INTO order_item VALUES (1, '바지2');
INSERT INTO order_item VALUES (1, '바지3');
-- 이렇게 하면 각 행에 하나의 값만 들어감

2) 2차 정규화

복합키를 사용하는 테이블에서, 복합키의 일부만으로 결정되는 컬럼이 있다면 분리해야 한다.

즉, 모든 컬럼이 PK 전체에 종속되어야 한다.

-- 잘못된 예시
-- 이름+맛이 복합키인데, 제조사는 이름만 알아도 결정됨 (부분 종속)
-- 이름PK | 맛PK  | 가격 | 제조사
-- 포카칩  | 기본맛 | 1600 | 오리온
-- 포카칩  | 양파맛 | 1400 | 오리온

-- 2차 정규화 후: 제조사를 별도 테이블로 분리
CREATE TABLE snack (
    snack_name VARCHAR(50),
    flavor VARCHAR(50),
    price INT,
    PRIMARY KEY (snack_name, flavor)
);

CREATE TABLE snack_maker (
    snack_name VARCHAR(50) PRIMARY KEY,
    maker VARCHAR(50)
);

INSERT INTO snack VALUES ('포카칩', '기본맛', 1600);
INSERT INTO snack VALUES ('포카칩', '양파맛', 1400);
INSERT INTO snack VALUES ('포테토칩', '오리지널', 1500);

INSERT INTO snack_maker VALUES ('포카칩', '오리온');
INSERT INTO snack_maker VALUES ('포테토칩', '농심');

3) 3차 정규화

PK가 아닌 컬럼이 다른 컬럼을 결정하는 관계가 있다면 분리해야 한다.

즉, PK → 다른 컬럼 → 또 다른 컬럼처럼 이어지는 종속 관계를 없애야 한다.

-- 잘못된 예시
-- 회원번호 -> 우편번호 -> 시, 구, 동  (이행 종속)
-- 회원번호PK | 이름  | 시  | 구   | 동    | 우편번호
-- 1          | 짱구  | 대전 | 서구 | 둔산동 | 11111
-- 2          | 철수  | 서울 | 노원구 | 상계동 | 22222

-- 3차 정규화 후: 주소 정보를 별도 테이블로 분리
CREATE TABLE member (
    member_id INT PRIMARY KEY,
    name VARCHAR(50),
    zip_code VARCHAR(10),
    FOREIGN KEY (zip_code) REFERENCES address(zip_code)
);

CREATE TABLE address (
    zip_code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(50),      -- 시
    district VARCHAR(50),  -- 구
    dong VARCHAR(50)       -- 동
);

INSERT INTO address VALUES ('11111', '대전', '서구', '둔산동');
INSERT INTO address VALUES ('22222', '서울', '노원구', '상계동');

INSERT INTO member VALUES (1, '짱구', '11111');
INSERT INTO member VALUES (2, '철수', '22222');

함수적 종속 (Functional Dependency)

정규화를 하려면 컬럼 간의 관계를 파악해야 하는데, 이 관계를 함수적 종속이라고 한다.

수학에서 y = 2x라는 함수가 있을 때, x값을 알면 y값이 결정된다. 이때 x는 결정자, y는 종속자다.

데이터베이스에서도 마찬가지다:

  • "생일을 알면 나이를 알 수 있다" → 나이 컬럼은 생일 컬럼에 함수적으로 종속된다.
  • "학번을 알면 학생 이름을 알 수 있다" → 이름 컬럼은 학번 컬럼에 함수적으로 종속된다.

정규화의 목표는 하나의 테이블 안에 하나의 함수적 종속성만 존재하도록 만드는 것이다.

4. 조인 (JOIN)

조인이란 여러 테이블에 흩어져 있는 데이터를 하나로 합쳐서 조회하는 것이다.

정규화를 하다 보면 테이블이 여러 개로 쪼개지는데, 실제로 데이터를 볼 때는 합쳐서 봐야 할 때가 많다. 그럴 때 조인을 사용한다.

예를 들어 학교 DB에 학생 테이블과 반 테이블이 나뉘어 있는데, "어느 반의 어느 학생인지"를 보고 싶을 때 조인을 쓴다.

SQL 실행 순서: FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

1) EQUI 조인 (동등 조인, 등가 조인)

WHERE 절에서 =를 사용하여 두 테이블을 연결하는 방법이다.

-- team 테이블과 player 테이블을 team_id로 연결
SELECT p.player_name, t.team_name
FROM player p, team t
WHERE p.team_id = t.team_id;

-- 결과: 선수 이름과 해당 선수의 소속 팀 이름이 함께 나옴
-- 손흥민 | 울산현대
-- 박지성 | 서울FC

2) INNER JOIN (내부 조인)

ON 키워드를 사용하여 두 테이블을 연결하는 방법이다. 두 테이블 모두에 데이터가 존재하는 행만 결과에 포함된다.

SELECT p.player_name, t.team_name
FROM player p
INNER JOIN team t ON p.team_id = t.team_id;

-- JOIN만 써도 INNER JOIN과 동일하게 동작한다
SELECT p.player_name, t.team_name
FROM player p
JOIN team t ON p.team_id = t.team_id;

아래 그림처럼 두 테이블의 교집합에 해당하는 데이터만 가져온다.

player 테이블    team 테이블
   [A]    [교집합]    [B]
          INNER JOIN은 교집합 부분만 반환

3) OUTER JOIN (외부 조인)

교집합뿐만 아니라 한쪽 테이블에만 있는 데이터도 포함해서 가져오는 방법이다.

-- LEFT OUTER JOIN: 왼쪽 테이블 전체 + 오른쪽 테이블에서 일치하는 것
-- 오른쪽 테이블에 없으면 NULL로 채움
SELECT p.player_name, t.team_name
FROM player p
LEFT OUTER JOIN team t ON p.team_id = t.team_id;

-- 결과 예시 (팀에 소속되지 않은 선수도 포함됨)
-- 손흥민  | 울산현대
-- 박지성  | 서울FC
-- 최신입  | NULL    <-- team_id가 없는 선수도 포함됨

-- RIGHT OUTER JOIN: 오른쪽 테이블 전체 + 왼쪽 테이블에서 일치하는 것
SELECT p.player_name, t.team_name
FROM player p
RIGHT OUTER JOIN team t ON p.team_id = t.team_id;

-- 결과 예시 (선수가 없는 팀도 포함됨)
-- 손흥민  | 울산현대
-- NULL    | 부산아이콘  <-- 아직 선수가 없는 팀도 포함됨

4) UNION (합집합)

두 SELECT 결과를 위아래로 합쳐서 하나로 만드는 것이다.

단, 두 쿼리의 컬럼 개수와 데이터 타입이 반드시 일치해야 한다.

-- UNION: 중복 제거 O, 정렬 발생
SELECT name FROM student_2023
UNION
SELECT name FROM student_2024;
-- 두 반에 같은 이름이 있어도 한 번만 나옴

-- UNION ALL: 중복 제거 X, 그냥 합치기만 함
SELECT name FROM student_2023
UNION ALL
SELECT name FROM student_2024;
-- 두 반에 같은 이름이 있으면 두 번 나옴

5) EXCEPT (차집합)

한 테이블 결과에서 다른 테이블 결과를 빼는 것이다. MySQL 8버전부터 사용 가능하다.

-- 2023년 학생 중 2024년에도 재학 중인 학생을 제외한 결과
SELECT name FROM student_2023
EXCEPT
SELECT name FROM student_2024;
-- 2023년에만 있고 2024년에는 없는 학생만 나옴 (졸업/자퇴 등)

5. TCL (Transaction Control Language, 트랜잭션 제어어)

트랜잭션이란?

트랜잭션은 하나의 작업 단위다. 여러 SQL문을 묶어서 "이것들은 모두 성공하거나 모두 실패해야 한다"고 처리하는 개념이다.

은행 이체를 예로 들면 "A 계좌에서 10만원 빼기" + "B 계좌에 10만원 더하기"가 하나의 트랜잭션이다. 둘 중 하나만 성공하면 큰 문제가 생기므로 반드시 둘 다 성공하거나 둘 다 취소되어야 한다.

1) COMMIT

모든 작업을 데이터베이스에 확정 반영하는 명령어다. 저장 버튼을 누르는 것과 같다.

START TRANSACTION;  -- 트랜잭션 시작

UPDATE account SET balance = balance - 100000 WHERE account_id = 'A';  -- A에서 10만원 출금
UPDATE account SET balance = balance + 100000 WHERE account_id = 'B';  -- B에 10만원 입금

COMMIT;  -- 모든 작업을 확정. DB에 영구 저장됨

2) ROLLBACK

작업하다가 문제가 생겼을 때 모든 변경사항을 취소하고 이전 상태로 되돌리는 명령어다. 게임에서 세이브 파일로 되돌아가는 것과 비슷하다.

START TRANSACTION;

UPDATE account SET balance = balance - 100000 WHERE account_id = 'A';  -- A에서 출금

-- 여기서 오류 발생! B 계좌가 존재하지 않음
UPDATE account SET balance = balance + 100000 WHERE account_id = 'B';  -- 실패!

ROLLBACK;  -- 모든 변경사항 취소. A 계좌도 원래대로 돌아감

3) SAVEPOINT (저장점)

트랜잭션을 중간에 저장점을 만들어서 부분적으로 되돌릴 수 있게 하는 것이다.

게임에서 중간 저장을 여러 번 해두고, 원하는 시점으로 돌아가는 것과 같다.

START TRANSACTION;

INSERT INTO order_list VALUES (1, '상품A', 10000);  -- 주문 1 추가
SAVEPOINT sp1;  -- 저장점 sp1 생성

INSERT INTO order_list VALUES (2, '상품B', 20000);  -- 주문 2 추가
SAVEPOINT sp2;  -- 저장점 sp2 생성

INSERT INTO order_list VALUES (3, '상품C', 30000);  -- 주문 3 추가

-- 주문 3이 잘못되었다. sp2로 되돌리고 싶다면
ROLLBACK TO sp2;  -- sp2 이후 작업(주문3)만 취소, 주문1과 주문2는 살아있음

COMMIT;  -- 주문1, 주문2만 최종 저장

6. DCL (Data Control Language, 데이터 제어어)

DCL은 데이터베이스 사용 권한을 관리하는 명령어다. 마치 학교에서 선생님만 성적을 수정할 수 있고, 학생은 자기 성적만 볼 수 있게 제한하는 것과 같다.

1) GRANT (권한 부여)

특정 사용자에게 데이터베이스 작업 권한을 주는 것이다.

-- 사용자 'teacher'에게 student 테이블의 SELECT, INSERT, UPDATE 권한 부여
GRANT SELECT, INSERT, UPDATE ON school.student TO 'teacher'@'localhost';

-- 사용자 'readonly_user'에게 조회만 허용
GRANT SELECT ON school.* TO 'readonly_user'@'localhost';

-- 모든 권한을 주고 싶다면
GRANT ALL PRIVILEGES ON school.* TO 'admin'@'localhost';

2) REVOKE (권한 회수)

이미 부여된 권한을 다시 빼앗는 것이다.

-- teacher에게 줬던 INSERT 권한 회수 (이제 teacher는 데이터 추가 불가)
REVOKE INSERT ON school.student FROM 'teacher'@'localhost';

-- readonly_user의 모든 권한 회수
REVOKE ALL PRIVILEGES ON school.* FROM 'readonly_user'@'localhost';

전체 개념 정리

분류개념핵심 설명
제약조건PK각 행을 구별하는 유일한 값, NULL/중복 불가
제약조건FK다른 테이블의 PK를 참조, NULL/중복 허용
제약조건NOT NULL빈 값 불허
제약조건CHECK범위/조건 제한
제약조건DEFAULT값 없을 때 자동 입력
제약조건복합키두 컬럼 이상을 묶어 PK로 사용
무결성개체 무결성PK는 중복/NULL 없어야 함
무결성참조 무결성FK는 참조 테이블에 실제 존재해야 함
무결성도메인 무결성값은 정해진 형식과 범위를 따라야 함
정규화1차한 칸에 한 값만
정규화2차복합키 일부에만 종속된 컬럼 분리
정규화3차PK 아닌 컬럼이 다른 컬럼 결정하는 것 분리
조인INNER JOIN두 테이블 교집합
조인LEFT/RIGHT OUTER JOIN교집합 + 한쪽 테이블 전체
조인UNION두 쿼리 결과 합치기 (중복 제거)
조인UNION ALL두 쿼리 결과 합치기 (중복 허용)
조인EXCEPT차집합
TCLCOMMIT작업 확정 저장
TCLROLLBACK작업 전체 취소
TCLSAVEPOINT중간 저장점 지정
DCLGRANT권한 부여
DCLREVOKE권한 회수
profile
sungsimdangmascot
성심당마스코트
이전 포스트

오답정리

다음 포스트

20260506(27일차)

0개의 댓글