🎯 데이터 무결성, 왜 중요할까?

안녕! 오늘은 데이터베이스에서 정말정말 중요한 개념인 데이터 무결성과 제약조건에 대해 이야기해볼게. 😊

상상해봐. 너가 온라인 쇼핑몰을 운영하는데, 어느 날 고객 데이터베이스를 확인했더니 이메일 주소가 없는 회원이 있고, 주문 테이블에는 존재하지 않는 상품 번호가 들어가 있고, 같은 주문번호가 중복으로 여러 개 있다면? 🤯

이건 단순히 데이터가 '지저분하다'는 수준이 아니야. 시스템 전체가 오작동할 수 있는 심각한 문제지! 바로 이런 상황을 방지하기 위해 우리는 데이터 무결성(Data Integrity)이라는 개념을 사용해.

🔐 개체 무결성 (Entity Integrity)

첫 번째로 알아볼 건 개체 무결성이야. 이건 테이블의 각 행(row)을 고유하게 식별할 수 있어야 한다는 원칙이지.

생각해봐. 학생 명단에 학번이 없거나, 같은 학번을 가진 학생이 여러 명 있다면? 누가 누군지 구분할 수가 없잖아! 😱

실제 SQL로 어떻게 구현하는지 볼까? 🤓

-- 학생 테이블 생성 (기본키 설정)
CREATE TABLE students (
    student_id INT PRIMARY KEY,  -- 기본키 지정
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    enrollment_date DATE
);

-- 또는 테이블 레벨에서 기본키 지정
CREATE TABLE students (
    student_id INT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    enrollment_date DATE,
    CONSTRAINT pk_student PRIMARY KEY (student_id)
);

-- 복합 기본키 (여러 컬럼 조합)
CREATE TABLE course_enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)  -- 두 컬럼 조합이 기본키
);

위 코드에서 PRIMARY KEY 제약조건을 사용하면, 데이터베이스가 자동으로:
✅ NULL 값 입력을 차단해
✅ 중복된 값 입력을 차단해
✅ 자동으로 인덱스를 생성해서 검색 속도를 높여줘

🔗 참조 무결성 (Referential Integrity)

이제 두 번째, 참조 무결성에 대해 알아보자! 이건 테이블 간의 관계를 유지하는 규칙이야.

예를 들어볼게. 주문 테이블에 "상품번호 999번을 주문했다"는 데이터가 있는데, 상품 테이블에는 999번 상품이 없다면? 이건 말이 안 되는 상황이지! 🤔

참조 무결성은 바로 이런 "존재하지 않는 데이터를 참조하는 상황"을 방지해줘.

-- 상품 테이블 (부모 테이블)
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    stock_quantity INT
);

-- 주문 테이블 (자식 테이블)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    quantity INT,
    CONSTRAINT fk_product 
        FOREIGN KEY (product_id) 
        REFERENCES products(product_id)
);

-- 이제 이런 상황이 발생해:
INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (1, 100, 999, 2);  -- ❌ 에러! product_id 999는 products 테이블에 없음!

외래키 제약조건을 설정하면 데이터베이스가 자동으로 참조 무결성을 검사해줘. 존재하지 않는 상품을 주문하려고 하면 에러가 발생하지! 💪

🎮 참조 무결성 옵션들

외래키를 설정할 때 부모 테이블의 데이터가 삭제되거나 수정될 때 어떻게 할지 정할 수 있어. 이게 진짜 중요한데, 실무에서 많이 사용하거든! 😎

-- CASCADE 예시: 상품 삭제 시 관련 주문도 삭제
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    CONSTRAINT fk_product_cascade
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE CASCADE  -- 상품 삭제 시 주문도 삭제
        ON UPDATE CASCADE  -- 상품 ID 변경 시 주문의 ID도 변경
);

-- SET NULL 예시: 카테고리 삭제 시 상품의 카테고리를 NULL로
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    CONSTRAINT fk_category_null
        FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
        ON DELETE SET NULL  -- 카테고리 삭제 시 NULL로 설정
);

-- RESTRICT 예시: 주문이 있는 고객은 삭제 불가
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_customer_restrict
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT  -- 주문이 있으면 고객 삭제 불가
);

📊 도메인 무결성 (Domain Integrity)

세 번째는 도메인 무결성이야! 이건 각 컬럼에 들어갈 수 있는 값의 범위나 형식을 제한하는 거지.

예를 들어 나이 컬럼에 -50이나 300 같은 말도 안 되는 값이 들어가면 안 되잖아? 또는 이메일 형식이 아닌 값이 이메일 컬럼에 들어가는 것도 막아야 하고! 🎯

🛠️ 도메인 무결성을 위한 제약조건들

도메인 무결성을 지키기 위해 여러 가지 제약조건을 사용할 수 있어. 하나씩 살펴보자!

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,  -- 필수 입력
    email VARCHAR(100) NOT NULL,    -- 필수 입력
    phone VARCHAR(20),               -- 선택 입력 (NULL 가능)
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,  -- 중복 불가
    email VARCHAR(100) NOT NULL UNIQUE,    -- 중복 불가
    phone VARCHAR(20) UNIQUE                -- NULL은 허용하되 값이 있으면 중복 불가
);

-- 또는 테이블 레벨에서 지정
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    CONSTRAINT uq_username UNIQUE (username),
    CONSTRAINT uq_email UNIQUE (email)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 70),  -- 나이는 18~70세
    salary DECIMAL(10, 2) CHECK (salary > 0),  -- 급여는 양수
    email VARCHAR(100) CHECK (email LIKE '%@%.%'),  -- 이메일 형식 체크
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),  -- M, F, O 중 하나
    hire_date DATE CHECK (hire_date <= CURRENT_DATE)  -- 입사일은 오늘 이전
);

-- 복잡한 CHECK 제약조건
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    discount_price DECIMAL(10, 2),
    stock_quantity INT,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount_price IS NULL OR discount_price < price),
    CONSTRAINT chk_stock CHECK (stock_quantity >= 0)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,  -- 기본값: 오늘 날짜
    status VARCHAR(20) DEFAULT 'PENDING',  -- 기본값: 'PENDING'
    is_paid BOOLEAN DEFAULT FALSE,         -- 기본값: FALSE
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 사용 예시
INSERT INTO orders (order_id, customer_id)
VALUES (1, 100);
-- order_date, status, is_paid, created_at는 자동으로 기본값 설정됨!

🎨 사용자 정의 무결성 (User-Defined Integrity)

마지막으로 사용자 정의 무결성이야! 이건 비즈니스 규칙에 따라 우리가 직접 정의하는 제약조건이지.

예를 들어 "할인가는 정가의 50%보다 낮을 수 없다"거나 "주문 수량은 재고 수량을 초과할 수 없다" 같은 복잡한 비즈니스 규칙을 구현할 수 있어! 🎯

🔧 트리거(Trigger)를 활용한 무결성

트리거는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생했을 때 자동으로 실행되는 코드야. 복잡한 비즈니스 로직을 구현할 때 정말 유용하지! 😎

-- 재고 수량 체크 트리거
CREATE TRIGGER check_stock_before_order
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE available_stock INT;
    
    -- 현재 재고 확인
    SELECT stock_quantity INTO available_stock
    FROM products
    WHERE product_id = NEW.product_id;
    
    -- 재고가 부족하면 에러 발생
    IF NEW.quantity > available_stock THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '재고가 부족합니다!';
    END IF;
END;

-- 주문 후 재고 자동 감소 트리거
CREATE TRIGGER update_stock_after_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE product_id = NEW.product_id;
END;

-- 할인가 검증 트리거
CREATE TRIGGER validate_discount_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.discount_price IS NOT NULL THEN
        -- 할인가는 정가의 50% 이상이어야 함
        IF NEW.discount_price < (NEW.price * 0.5) THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '할인가는 정가의 50% 이상이어야 합니다!';
        END IF;
    END IF;
END;

📝 저장 프로시저(Stored Procedure)를 활용한 무결성

저장 프로시저를 사용하면 복잡한 비즈니스 로직을 캡슐화할 수 있어. 데이터 입력 전에 여러 가지 검증을 수행할 수 있지! 💪

-- 안전한 주문 생성 프로시저
DELIMITER //

CREATE PROCEDURE create_order_safely(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_customer_exists INT;
    
    -- 트랜잭션 시작
    START TRANSACTION;
    
    -- 1. 고객 존재 여부 확인
    SELECT COUNT(*) INTO v_customer_exists
    FROM customers
    WHERE customer_id = p_customer_id;
    
    IF v_customer_exists = 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '존재하지 않는 고객입니다!';
    END IF;
    
    -- 2. 재고 확인
    SELECT stock_quantity, price INTO v_stock, v_price
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE;  -- 동시성 제어를 위한 락
    
    IF v_stock < p_quantity THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '재고가 부족합니다!';
    END IF;
    
    -- 3. 주문 생성
    INSERT INTO orders (customer_id, product_id, quantity, total_price, order_date)
    VALUES (p_customer_id, p_product_id, p_quantity, v_price * p_quantity, CURRENT_DATE);
    
    -- 4. 재고 감소
    UPDATE products
    SET stock_quantity = stock_quantity - p_quantity
    WHERE product_id = p_product_id;
    
    -- 트랜잭션 커밋
    COMMIT;
END //

DELIMITER ;

-- 사용 예시
CALL create_order_safely(100, 5, 2);

⚡ 제약조건 관리하기

제약조건을 만들었으면 관리도 할 줄 알아야겠지? 제약조건을 추가하고, 수정하고, 삭제하는 방법을 알아보자! 🛠️

➕ 기존 테이블에 제약조건 추가하기

-- PRIMARY KEY 추가
ALTER TABLE users
ADD PRIMARY KEY (user_id);

-- FOREIGN KEY 추가
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- UNIQUE 제약조건 추가
ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email);

-- CHECK 제약조건 추가
ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18 AND age <= 70);

-- NOT NULL 제약조건 추가 (컬럼 수정)
ALTER TABLE users
MODIFY email VARCHAR(100) NOT NULL;

-- DEFAULT 값 추가
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'PENDING';

🗑️ 제약조건 삭제하기

-- PRIMARY KEY 삭제
ALTER TABLE users
DROP PRIMARY KEY;

-- FOREIGN KEY 삭제
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

-- UNIQUE 제약조건 삭제
ALTER TABLE users
DROP INDEX uq_email;

-- CHECK 제약조건 삭제
ALTER TABLE employees
DROP CHECK chk_age;

-- NOT NULL 제약조건 제거 (컬럼 수정)
ALTER TABLE users
MODIFY email VARCHAR(100) NULL;

-- DEFAULT 값 제거
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

🔍 제약조건 조회하기

현재 테이블에 어떤 제약조건이 설정되어 있는지 확인하는 방법도 알아두면 좋아! 📋

-- MySQL에서 제약조건 조회
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.TABLE_SCHEMA = 'your_database_name'
    AND tc.TABLE_NAME = 'your_table_name';

-- 외래키 상세 정보 조회
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

-- PostgreSQL에서 제약조건 조회
SELECT
    con.conname AS constraint_name,
    con.contype AS constraint_type,
    tbl.relname AS table_name,
    col.attname AS column_name
FROM pg_constraint con
JOIN pg_class tbl ON con.conrelid = tbl.oid
JOIN pg_attribute col ON col.attrelid = tbl.oid
WHERE tbl.relname = 'your_table_name';

🎯 실전 예제: 온라인 쇼핑몰 데이터베이스

이제 배운 내용을 종합해서 실제 온라인 쇼핑몰 데이터베이스를 설계해보자! 모든 종류의 무결성과 제약조건을 적용해볼 거야. 🛒

-- 1. 고객 테이블
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,  -- 개체 무결성
    username VARCHAR(50) NOT NULL UNIQUE,        -- 도메인 무결성
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    birth_date DATE,
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%'),
    CONSTRAINT chk_birth_date CHECK (birth_date <= CURRENT_DATE)
);

-- 2. 카테고리 테이블
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    parent_category_id INT,
    description TEXT,
    CONSTRAINT fk_parent_category
        FOREIGN KEY (parent_category_id)
        REFERENCES categories(category_id)
        ON DELETE SET NULL  -- 부모 카테고리 삭제 시 NULL로
);

-- 3. 상품 테이블
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category_id INT,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    discount_price DECIMAL(10, 2),
    stock_quantity INT NOT NULL DEFAULT 0,
    min_order_quantity INT DEFAULT 1,
    max_order_quantity INT DEFAULT 999,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 제약조건들
    CONSTRAINT fk_category
        FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
        ON DELETE SET NULL,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (
        discount_price IS NULL OR 
        (discount_price > 0 AND discount_price < price)
    ),
    CONSTRAINT chk_stock CHECK (stock_quantity >= 0),
    CONSTRAINT chk_order_quantity CHECK (
        min_order_quantity > 0 AND 
        max_order_quantity >= min_order_quantity
    )
);

-- 4. 주문 테이블
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'PENDING',
    total_amount DECIMAL(10, 2) NOT NULL,
    shipping_address VARCHAR(255) NOT NULL,
    payment_method VARCHAR(20),
    
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT,  -- 주문이 있는 고객은 삭제 불가
    CONSTRAINT chk_status CHECK (
        status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'DELIVERED', 'CANCELLED')
    ),
    CONSTRAINT chk_total_amount CHECK (total_amount >= 0),
    CONSTRAINT chk_payment_method CHECK (
        payment_method IN ('CARD', 'BANK_TRANSFER', 'CASH', 'PAYPAL')
    )
);

-- 5. 주문 상세 테이블
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    
    CONSTRAINT fk_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE CASCADE,  -- 주문 삭제 시 주문 상세도 삭제
    CONSTRAINT fk_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE RESTRICT,  -- 주문된 상품은 삭제 불가
    CONSTRAINT chk_quantity CHECK (quantity > 0),
    CONSTRAINT chk_unit_price CHECK (unit_price > 0),
    CONSTRAINT chk_subtotal CHECK (subtotal = quantity * unit_price),
    CONSTRAINT uq_order_product UNIQUE (order_id, product_id)  -- 같은 주문에 같은 상품 중복 방지
);

-- 6. 리뷰 테이블
CREATE TABLE reviews (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    rating INT NOT NULL,
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_review_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_review_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE,
    CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5),
    CONSTRAINT uq_customer_product UNIQUE (customer_id, product_id)  -- 고객당 상품 1개 리뷰만
);

🚀 고급 기법: 복합 제약조건과 조건부 제약

이제 좀 더 고급 기법을 알아볼까? 실무에서 정말 유용한 테크닉들이야! 💡

🔗 복합 제약조건 (Composite Constraints)

여러 컬럼을 조합해서 제약조건을 만들 수 있어. 이게 생각보다 엄청 유용해! 😎

-- 복합 UNIQUE: 같은 날짜에 같은 고객이 같은 상품을 여러 번 주문할 수 없음
CREATE TABLE daily_orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    order_date DATE NOT NULL,
    quantity INT NOT NULL,
    CONSTRAINT uq_daily_order UNIQUE (customer_id, product_id, order_date)
);

-- 복합 CHECK: 시작일과 종료일 검증
CREATE TABLE promotions (
    promotion_id INT AUTO_INCREMENT PRIMARY KEY,
    promotion_name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    discount_rate DECIMAL(5, 2),
    CONSTRAINT chk_date_range CHECK (end_date >= start_date),
    CONSTRAINT chk_discount_rate CHECK (
        discount_rate > 0 AND discount_rate <= 100
    )
);

-- 복합 외래키: 여러 컬럼을 조합한 참조
CREATE TABLE course_schedule (
    schedule_id INT AUTO_INCREMENT PRIMARY KEY,
    course_code VARCHAR(10),
    semester VARCHAR(10),
    classroom VARCHAR(20),
    CONSTRAINT fk_course FOREIGN KEY (course_code, semester)
        REFERENCES courses(course_code, semester)
);

🎭 조건부 제약조건

특정 조건에서만 적용되는 제약조건도 만들 수 있어! 이건 진짜 강력한 기능이야. 🔥

-- 조건부 CHECK: 할인 상품일 때만 할인가 필수
CREATE TABLE products_advanced (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    is_on_sale BOOLEAN DEFAULT FALSE,
    discount_price DECIMAL(10, 2),
    
    CONSTRAINT chk_discount_required CHECK (
        (is_on_sale = FALSE) OR 
        (is_on_sale = TRUE AND discount_price IS NOT NULL AND discount_price < price)
    )
);

-- 조건부 UNIQUE: 활성 상태인 것만 중복 불가
-- (MySQL 8.0.13 이상에서 지원하는 함수 인덱스 활용)
CREATE TABLE user_sessions (
    session_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_type VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 활성 세션만 중복 방지하는 인덱스
CREATE UNIQUE INDEX idx_active_session 
ON user_sessions(user_id, device_type, (CASE WHEN is_active THEN 1 END));

-- 조건부 외래키: NULL이 아닐 때만 참조 검증
CREATE TABLE optional_manager (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INT,  -- NULL 허용
    CONSTRAINT fk_manager
        FOREIGN KEY (manager_id)
        REFERENCES optional_manager(employee_id)
        ON DELETE SET NULL
    -- manager_id가 NULL이면 외래키 검증 안 함!
);

⏰ 시간 기반 제약조건

-- 영업 시간 체크
CREATE TABLE reservations (
    reservation_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    reservation_time DATETIME NOT NULL,
    
    CONSTRAINT chk_business_hours CHECK (
        HOUR(reservation_time) >= 9 AND 
        HOUR(reservation_time) < 22
    ),
    CONSTRAINT chk_future_reservation CHECK (
        reservation_time > NOW()
    )
);

-- 유효 기간 체크
CREATE TABLE coupons (
    coupon_id INT AUTO_INCREMENT PRIMARY KEY,
    coupon_code VARCHAR(20) NOT NULL UNIQUE,
    discount_amount DECIMAL(10, 2) NOT NULL,
    valid_from DATE NOT NULL,
    valid_until DATE NOT NULL,
    is_used BOOLEAN DEFAULT FALSE,
    
    CONSTRAINT chk_valid_period CHECK (valid_until >= valid_from),
    CONSTRAINT chk_not_expired CHECK (
        is_used = TRUE OR valid_until >= CURRENT_DATE
    )
);

🐛 제약조건 위반 처리하기

제약조건을 설정했다면, 위반했을 때 어떻게 처리할지도 알아야겠지? 에러 메시지를 잘 활용하면 사용자 경험이 훨씬 좋아져! 😊

🎯 에러 처리 전략

-- 1. 트랜잭션과 함께 사용하기
START TRANSACTION;

BEGIN
    -- 여러 작업 수행
    INSERT INTO customers (username, email) 
    VALUES ('john_doe', 'john@example.com');
    
    INSERT INTO orders (customer_id, total_amount)
    VALUES (LAST_INSERT_ID(), 50000);
    
    -- 모든 작업이 성공하면 커밋
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 에러 발생 시 롤백
        ROLLBACK;
        -- 에러 로깅
        INSERT INTO error_log (error_message, error_time)
        VALUES (SQLERRM, NOW());
END;

-- 2. 저장 프로시저에서 에러 처리
DELIMITER //

CREATE PROCEDURE safe_insert_product(
    IN p_name VARCHAR(100),
    IN p_price DECIMAL(10,2),
    IN p_stock INT,
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 에러 발생 시 처리
        GET DIAGNOSTICS CONDITION 1
            @sqlstate = RETURNED_SQLSTATE,
            @errno = MYSQL_ERRNO,
            @text = MESSAGE_TEXT;
        
        SET p_result = CONCAT('에러 발생: ', @text);
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- 유효성 검사
    IF p_price <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '가격은 0보다 커야 합니다!';
    END IF;
    
    IF p_stock < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '재고는 음수일 수 없습니다!';
    END IF;
    
    -- 데이터 삽입
    INSERT INTO products (product_name, price, stock_quantity)
    VALUES (p_name, p_price, p_stock);
    
    COMMIT;
    SET p_result = '상품이 성공적으로 등록되었습니다!';
END //

DELIMITER ;

-- 사용 예시
CALL safe_insert_product('노트북', 1500000, 10, @result);
SELECT @result;

📱 애플리케이션 레벨에서의 처리

데이터베이스 제약조건과 함께 애플리케이션 레벨에서도 검증을 하면 더 좋은 사용자 경험을 제공할 수 있어! 🎨

-- Python 예시 (Flask + SQLAlchemy)
from flask import Flask, request, jsonify
from sqlalchemy.exc import IntegrityError
import re

@app.route('/api/users', methods=['POST'])
def create_user():
    try:
        data = request.get_json()
        
        # 1. 애플리케이션 레벨 검증
        if not data.get('email'):
            return jsonify({'error': '이메일은 필수입니다!'}), 400
        
        # 이메일 형식 검증
        email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
        if not re.match(email_pattern, data['email']):
            return jsonify({'error': '올바른 이메일 형식이 아닙니다!'}), 400
        
        # 2. 데이터베이스 삽입
        new_user = User(
            username=data['username'],
            email=data['email']
        )
        db.session.add(new_user)
        db.session.commit()
        
        return jsonify({'message': '사용자가 생성되었습니다!'}), 201
        
    except IntegrityError as e:
        db.session.rollback()
        
        # 제약조건 위반 타입별 처리
        error_message = str(e.orig)
        
        if 'Duplicate entry' in error_message:
            if 'email' in error_message:
                return jsonify({'error': '이미 사용 중인 이메일입니다!'}), 409
            elif 'username' in error_message:
                return jsonify({'error': '이미 사용 중인 사용자명입니다!'}), 409
        
        elif 'foreign key constraint' in error_message.lower():
            return jsonify({'error': '참조하는 데이터가 존재하지 않습니다!'}), 400
        
        elif 'check constraint' in error_message.lower():
            return jsonify({'error': '입력값이 유효하지 않습니다!'}), 400
        
        return jsonify({'error': '데이터베이스 오류가 발생했습니다!'}), 500
    
    except Exception as e:
        return jsonify({'error': '서버 오류가 발생했습니다!'}), 500

⚡ 성능 최적화 팁

제약조건은 데이터 무결성을 보장하지만, 잘못 사용하면 성능 문제가 생길 수 있어. 실무에서 꼭 알아야 할 최적화 팁을 알려줄게! 🚀

-- 좋은 예: 선택도가 높은 컬럼을 앞에
CREATE INDEX idx_order_search ON orders(status, customer_id, order_date);

-- 나쁜 예: 선택도가 낮은 컬럼을 앞에
CREATE INDEX idx_order_bad ON orders(status, order_date, customer_id);
-- status는 값의 종류가 적어서 선택도가 낮음!

-- MySQL에서 외래키 체크 임시 비활성화
SET FOREIGN_KEY_CHECKS = 0;

-- 대량 데이터 삽입
INSERT INTO orders (customer_id, product_id, quantity)
SELECT customer_id, product_id, quantity
FROM temp_orders;

-- 외래키 체크 다시 활성화
SET FOREIGN_KEY_CHECKS = 1;

-- 제약조건 위반 데이터 확인
SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- 나쁜 예: 매번 전체 테이블 스캔
CREATE TRIGGER update_product_rating
AFTER INSERT ON reviews
FOR EACH ROW
BEGIN
    UPDATE products
    SET average_rating = (
        SELECT AVG(rating)
        FROM reviews
        WHERE product_id = NEW.product_id
    )
    WHERE product_id = NEW.product_id;
END;

-- 좋은 예: 증분 업데이트
CREATE TRIGGER update_product_rating_optimized
AFTER INSERT ON reviews
FOR EACH ROW
BEGIN
    UPDATE products
    SET 
        total_rating = total_rating + NEW.rating,
        review_count = review_count + 1,
        average_rating = (total_rating + NEW.rating) / (review_count + 1)
    WHERE product_id = NEW.product_id;
END;

-- 날짜 기반 파티셔닝
CREATE TABLE orders_partitioned (
    order_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2),
    PRIMARY KEY (order_id, order_date),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

🎓 실무 베스트 프랙티스

마지막으로 실무에서 정말 중요한 베스트 프랙티스를 정리해볼게! 이건 내가 실제 프로젝트에서 배운 소중한 경험들이야. 💎

-- 좋은 예: 명확한 명명과 주석
CREATE TABLE products (
    product_id INT AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    discount_price DECIMAL(10, 2),
    stock_quantity INT NOT NULL DEFAULT 0,
    
    -- 기본키: 상품 고유 식별자
    CONSTRAINT pk_products PRIMARY KEY (product_id),
    
    -- 가격 검증: 정가는 0보다 커야 함
    CONSTRAINT chk_products_price CHECK (price > 0),
    
    -- 할인가 검증: 할인가는 정가보다 낮아야 함
    CONSTRAINT chk_products_discount CHECK (
        discount_price IS NULL OR 
        (discount_price > 0 AND discount_price < price)
    ),
    
    -- 재고 검증: 재고는 음수일 수 없음
    CONSTRAINT chk_products_stock CHECK (stock_quantity >= 0),
    
    -- 상품명 중복 방지
    CONSTRAINT uq_products_name UNIQUE (product_name)
) COMMENT='상품 정보 테이블 - 2024.01 생성';

🔍 실전 디버깅 가이드

제약조건 관련 에러가 발생했을 때 어떻게 해결할지 알아보자! 실무에서 자주 마주치는 상황들이야. 🔧

🐛 자주 발생하는 에러와 해결법

-- 문제 상황
INSERT INTO users (user_id, username) VALUES (1, 'john');
INSERT INTO users (user_id, username) VALUES (1, 'jane');  -- ❌ 에러!

-- 해결 방법 1: AUTO_INCREMENT 사용
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

INSERT INTO users (username) VALUES ('john');
INSERT INTO users (username) VALUES ('jane');  -- ✅ 자동으로 2 할당

-- 해결 방법 2: UUID 사용
CREATE TABLE users (
    user_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    username VARCHAR(50)
);

INSERT INTO users (username) VALUES ('john');  -- ✅ 자동으로 UUID 생성

-- 문제 상황
INSERT INTO orders (order_id, customer_id, product_id)
VALUES (1, 999, 1);  -- ❌ customer_id 999가 customers 테이블에 없음!

-- 해결 방법 1: 부모 데이터 먼저 확인
SELECT customer_id FROM customers WHERE customer_id = 999;
-- 결과가 없으면 먼저 고객 생성

-- 해결 방법 2: 존재하는 데이터만 삽입하는 쿼리
INSERT INTO orders (order_id, customer_id, product_id)
SELECT 1, c.customer_id, 1
FROM customers c
WHERE c.customer_id = 999;
-- 고객이 없으면 아무것도 삽입되지 않음

-- 해결 방법 3: 외래키 제약조건 확인 및 수정
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- 문제 상황
INSERT INTO employees (employee_id, name, age, salary)
VALUES (1, 'John', 15, 3000000);  -- ❌ age < 18

-- 해결 방법 1: 올바른 값 입력
INSERT INTO employees (employee_id, name, age, salary)
VALUES (1, 'John', 25, 3000000);  -- ✅

-- 해결 방법 2: 제약조건 확인
SELECT CONSTRAINT_NAME, CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE TABLE_NAME = 'employees';

-- 해결 방법 3: 필요시 제약조건 수정
ALTER TABLE employees
DROP CHECK chk_age;

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 16 AND age <= 70);

🔎 제약조건 위반 데이터 찾기

-- 1. 중복된 데이터 찾기 (UNIQUE 위반 가능성)
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 2. NULL 값 찾기 (NOT NULL 위반 가능성)
SELECT *
FROM products
WHERE product_name IS NULL OR price IS NULL;

-- 3. 참조 무결성 위반 찾기 (FOREIGN KEY 위반 가능성)
SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- 4. CHECK 제약조건 위반 가능성 찾기
SELECT *
FROM employees
WHERE age < 18 OR age > 70 OR salary <= 0;

-- 5. 범위를 벗어난 데이터 찾기
SELECT *
FROM products
WHERE discount_price >= price  -- 할인가가 정가보다 높거나 같음
   OR stock_quantity < 0;      -- 재고가 음수

🌟 마무리하며

와! 여기까지 읽었다면 정말 대단해! 👏

데이터 무결성과 제약조건은 데이터베이스 설계의 핵심이야. 처음에는 복잡하고 귀찮게 느껴질 수 있지만, 제대로 설정해두면:

✅ 데이터 품질이 향상돼
✅ 버그가 줄어들어
✅ 유지보수가 쉬워져
✅ 신뢰성 있는 시스템을 만들 수 있어

기억해! 제약조건은 귀찮은 게 아니라 미래의 나를 도와주는 친구야! 😊

이제 너도 데이터 무결성 마스터! 💪
실전 프로젝트에서 배운 내용을 적용해보고, 계속 연습하면서 실력을 키워나가자!

궁금한 점이 있거나 더 알고 싶은 내용이 있다면 언제든 질문해줘.
함께 성장하는 개발자가 되자! 🌱✨