제약 조건

-- 컬럼 레벨 정의
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name   VARCHAR(100) NOT NULL
);

-- 테이블 레벨 정의 (이름 지정)
CREATE TABLE employees (
    emp_id INT,
    name   VARCHAR(100) NOT NULL,
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);

-- 학생-과목 수강 테이블 (학생ID + 과목ID로 식별)
CREATE TABLE enrollment (
    student_id INT,
    course_id  INT,
    grade      VARCHAR(2),
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);

-- (1, 101, 'A')와 (1, 102, 'B')는 허용
-- (1, 101, 'A')와 (1, 101, 'B')는 중복 — 같은 학생이 같은 과목을 두 번 수강

자연키 (Natural Key):
  비즈니스 의미가 있는 컬럼을 기본키로 사용
  예: 주민등록번호, 이메일, ISBN

  장점: 별도의 ID 컬럼 불필요
  단점: 비즈니스 규칙 변경 시 키도 변경해야 함
        (예: 주민번호 수집 금지법)

대리키 (Surrogate Key):
  비즈니스 의미 없이 순수하게 식별만을 위한 컬럼
  예: AUTO_INCREMENT, SEQUENCE, UUID

  장점: 비즈니스 변화에 영향 없음, 조인 성능 우수
  단점: 별도의 ID 컬럼 필요, 비즈니스 의미 없음

Oracle:     UNIQUE INDEX (비클러스터드)
MySQL:      CLUSTERED INDEX (행 데이터가 PK 순서로 저장됨)
PostgreSQL: UNIQUE INDEX (비클러스터드)
SQL Server: CLUSTERED INDEX (기본, 변경 가능)

-- 컬럼 레벨
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id  INT REFERENCES users(user_id),
    amount   INT NOT NULL
);

-- 테이블 레벨 (이름 지정)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id  INT NOT NULL,
    amount   INT NOT NULL,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

CREATE TABLE exam_scores (
    score_id   INT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    score      INT,
    CONSTRAINT fk_scores_enrollment
        FOREIGN KEY (student_id, course_id)
        REFERENCES enrollment(student_id, course_id)
);

CREATE TABLE employees (
    emp_id     INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    manager_id INT,
    CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id)
        REFERENCES employees(emp_id)
);

-- 최상위 관리자 (사장)
INSERT INTO employees VALUES (1, '사장', NULL);
-- 부장 (사장의 부하)
INSERT INTO employees VALUES (2, '부장', 1);
-- 과장 (부장의 부하)
INSERT INTO employees VALUES (3, '과장', 2);

CREATE TABLE employees (
    emp_id   INT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    dept_id  INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

users → orders → order_items → shipments

users에 ON DELETE CASCADE가 설정되어 있다면:
DELETE FROM users WHERE user_id = 1;

→ user_id=1의 주문 삭제
  → 해당 주문의 주문상세 삭제
    → 해당 주문상세의 배송정보 삭제

한 명의 회원 삭제로 수백 건의 데이터가 연쇄 삭제!
→ 실무에서는 CASCADE를 신중하게 사용해야 합니다

CREATE TABLE users (
    user_id   INT PRIMARY KEY,
    email     VARCHAR(100) NOT NULL,
    phone     VARCHAR(20),
    CONSTRAINT uq_email UNIQUE (email),
    CONSTRAINT uq_phone UNIQUE (phone)
);

-- 같은 부서에 같은 직급은 하나만 (부서-직급 조합 유일)
CREATE TABLE positions (
    dept_id  INT,
    title    VARCHAR(50),
    emp_id   INT,
    CONSTRAINT uq_dept_title UNIQUE (dept_id, title)
);

CREATE TABLE users (
    user_id  INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,      -- 반드시 입력
    email    VARCHAR(100) NOT NULL,     -- 반드시 입력
    phone    VARCHAR(20)                -- NULL 허용
);

반드시 NOT NULL:
  * 기본키 (PK에 자동 포함)
  * 로그인에 필수인 컬럼 (username, email, password)
  * 비즈니스 로직에 반드시 필요한 값 (주문의 상품ID, 수량)
  * 상태값 (status, is_active 등)

NULL 허용 가능:
  * 선택적 입력 정보 (phone, address, bio)
  * 나중에 결정되는 값 (배송일, 결제일)
  * 외래키 (관계가 선택적인 경우)
  * 자기 참조 FK (최상위 노드는 부모가 없음)

CREATE TABLE employees (
    emp_id   INT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    age      INT CHECK (age >= 0 AND age <= 150),
    salary   INT,
    gender   CHAR(1),
    CONSTRAINT ck_salary CHECK (salary >= 0),
    CONSTRAINT ck_gender CHECK (gender IN ('M', 'F'))
);

-- 가능
CHECK (price >= 0)
CHECK (status IN ('ACTIVE', 'INACTIVE', 'DELETED'))
CHECK (start_date < end_date)          -- 같은 행의 다른 컬럼 참조 가능

-- 불가능
CHECK (salary < (SELECT max_salary FROM pay_grades WHERE ...))  -- 서브쿼리
CHECK (email NOT IN (SELECT email FROM blocked_emails))          -- 다른 테이블

SELECT VERSION();
-- 8.0.16 이상이어야 CHECK가 실제로 동작

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    status      VARCHAR(20) DEFAULT 'PENDING',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted  BOOLEAN DEFAULT FALSE
);

-- status, created_at, is_deleted를 생략하면 DEFAULT 값 적용
INSERT INTO orders (order_id) VALUES (1);
-- 결과: order_id=1, status='PENDING', created_at=현재시각, is_deleted=FALSE

CREATE TABLE employees (
    id         NUMBER,
    name       VARCHAR2(100),
    email      VARCHAR2(200),
    dept_id    NUMBER,
    salary     NUMBER,
    CONSTRAINT pk_emp         PRIMARY KEY (id),
    CONSTRAINT uq_emp_email   UNIQUE (email),
    CONSTRAINT fk_emp_dept    FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON DELETE SET NULL,
    CONSTRAINT ck_emp_salary  CHECK (salary >= 0),
    CONSTRAINT nn_emp_name    CHECK (name IS NOT NULL)
);

-- 시퀀스 생성
CREATE SEQUENCE emp_seq
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 9999999
    NOCACHE;        -- 캐시 없이 순차 생성

-- 사용
INSERT INTO employees (id, name)
VALUES (emp_seq.NEXTVAL, '김철수');

-- 현재 값 확인
SELECT emp_seq.CURRVAL FROM DUAL;

-- 시퀀스 수정
ALTER SEQUENCE emp_seq INCREMENT BY 10;

-- 시퀀스 삭제
DROP SEQUENCE emp_seq;

CREATE TABLE employees (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- id를 지정하지 않으면 자동 증가
INSERT INTO employees (name) VALUES ('김철수');
INSERT INTO employees (name) VALUES ('이영희');

-- 마지막 생성된 ID 확인
SELECT LAST_INSERT_ID();

-- AUTO_INCREMENT 값 재설정
ALTER TABLE employees AUTO_INCREMENT = 100;

-- SERIAL (레거시)
CREATE TABLE employees (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
-- 내부적으로 시퀀스 자동 생성

-- IDENTITY (SQL 표준, PostgreSQL 10+)
CREATE TABLE employees (
    id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- GENERATED BY DEFAULT: 명시적 값 지정 허용
CREATE TABLE employees (
    id   INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL
);

-- 테이블의 제약 조건 목록
SELECT constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

-- 제약 조건의 컬럼 정보
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';

-- 테이블 구조와 제약 조건 확인
SHOW CREATE TABLE employees;

-- information_schema에서 조회
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'employees';

-- 테이블의 제약 조건
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'employees'::regclass;

-- NOT NULL 추가 전: NULL 데이터 확인
SELECT COUNT(*) FROM employees WHERE phone IS NULL;

-- CHECK 추가 전: 조건 위반 데이터 확인
SELECT * FROM employees WHERE salary < 0;

-- UNIQUE 추가 전: 중복 데이터 확인
SELECT email, COUNT(*) FROM employees GROUP BY email HAVING COUNT(*) > 1;

-- FK 추가 전: 참조 무결성 위반 데이터 확인
SELECT e.dept_id FROM employees e
WHERE e.dept_id NOT IN (SELECT dept_id FROM departments);