테이블 설계와 제약조건

좋은 데이터베이스는 좋은 테이블 설계에서 시작합니다. 적절한 데이터 타입과 제약조건을 걸어 두면 잘못된 데이터가 애초에 들어오지 못하게 막을 수 있습니다. 테이블 구조를 정의하는 명령을 DDL(Data Definition Language) 이라고 합니다.

학습 목표

• CREATE/ALTER/DROP TABLE 로 테이블을 정의하고 변경합니다.
• 자주 쓰는 데이터 타입을 적절히 선택합니다.
• NOT NULL·DEFAULT·UNIQUE·PK·FK 제약을 적용합니다.
• AUTO_INCREMENT 로 자동 증가 키를 만듭니다.

주요 데이터 타입

분류	타입	설명
정수	INT, BIGINT, TINYINT	정수 (TINYINT(1) 은 흔히 불리언)
실수	DECIMAL(p,s), DOUBLE	고정/부동 소수
문자	VARCHAR(n), TEXT	가변 문자열 / 긴 텍스트
날짜	DATE, DATETIME, TIMESTAMP	날짜·시간
기타	JSON, ENUM(...)	JSON 문서 / 고정 후보값

💡 TIP — 금액은 오차가 없는 DECIMAL 을 쓰고 부동소수 DOUBLE 은 피하세요. 한글·이모지는 테이블/컬럼 문자셋을 utf8mb4 로 두어야 안전하게 저장됩니다. DATETIME 은 입력값을 그대로, TIMESTAMP 는 UTC 기준으로 저장됩니다.

CREATE TABLE

CREATE TABLE users (
  id          INT          AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(50)  NOT NULL,
  email       VARCHAR(100) UNIQUE,
  city        VARCHAR(30)  NOT NULL DEFAULT 'Unassigned',
  point       INT          NOT NULL DEFAULT 0,
  dept_id     INT          NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_user_dept FOREIGN KEY (dept_id)
    REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ENGINE=InnoDB 는 트랜잭션과 외래키를 지원하는 MySQL의 표준 스토리지 엔진입니다.

제약조건 정리

제약	의미
NOT NULL	빈 값 금지
DEFAULT	값 미지정 시 기본값
UNIQUE	중복 금지 (NULL은 여러 개 허용)
PRIMARY KEY	유일 + NOT NULL, 행 식별자
FOREIGN KEY	다른 테이블 PK 참조
CHECK	조건 만족 시만 입력 (8.0.16+ 부터 실제 강제)

AUTO_INCREMENT — 자동 증가

id INT AUTO_INCREMENT PRIMARY KEY  -- 삽입 시 자동으로 1씩 증가

값을 지정하지 않으면 자동으로 채워집니다. 시작값은 ALTER TABLE users AUTO_INCREMENT = 1000; 으로 바꿀 수 있습니다.

CHECK 제약 예시

ALTER TABLE products
ADD CONSTRAINT ck_price_positive CHECK (price > 0);

ALTER TABLE — 구조 변경

-- 열 추가
ALTER TABLE users ADD phone VARCHAR(20) NULL;

-- 열 타입/속성 변경
ALTER TABLE users MODIFY name VARCHAR(100) NOT NULL;

-- 열 이름까지 변경
ALTER TABLE users CHANGE phone mobile VARCHAR(20);

-- 열 삭제
ALTER TABLE users DROP COLUMN mobile;

-- 제약(인덱스) 추가
ALTER TABLE users ADD UNIQUE (email);

⚠️ 주의 — 운영 중인 큰 테이블의 ALTER 는 테이블 잠금이나 복사로 오래 걸릴 수 있습니다. 사용량이 적은 시간대에, 가능하면 사전에 테스트한 뒤 적용하세요.

DROP / 외래키와 참조 동작

DROP TABLE users;   -- 테이블 자체를 제거

외래키에는 부모가 삭제·수정될 때의 동작을 지정할 수 있습니다.

CONSTRAINT fk_user_dept FOREIGN KEY (dept_id)
  REFERENCES departments(id)
  ON DELETE SET NULL      -- 부서 삭제 시 dept_id를 NULL로
  ON UPDATE CASCADE       -- 부서 id 변경 시 함께 변경

옵션	동작
RESTRICT / NO ACTION	참조 중이면 삭제/수정 거부(기본)
CASCADE	함께 삭제/수정
SET NULL	참조 열을 NULL로

요약

• 데이터 타입은 용도에 맞게(금액은 DECIMAL, 문자셋은 utf8mb4) 선택합니다.
• 제약조건(NOT NULL·UNIQUE·PK·FK)으로 잘못된 데이터를 차단합니다.
• AUTO_INCREMENT 는 자동 증가 키를 생성합니다.
• ALTER TABLE 로 열·제약을 추가·변경·삭제하되, 큰 테이블은 주의합니다.
• 외래키는 ON DELETE/ON UPDATE 로 참조 동작을 정하며 InnoDB에서 동작합니다.

연습문제

1. 자동 증가 PK, NOT NULL 이름, UNIQUE 이메일을 가진 members 테이블을 만드세요.
2. products 에 price > 0 을 강제하는 CHECK 제약을 추가하세요.
3. members 에 가입 시각 열 joined_at 을 기본값 현재시각으로 추가하세요.
4. 부서 삭제 시 사용자의 dept_id 가 NULL이 되도록 외래키를 정의하세요.

힌트 — 3번은 DEFAULT CURRENT_TIMESTAMP, 4번은 ON DELETE SET NULL 입니다.