DDL 과 제약 조건

2. 핵심 원리

2.1 CREATE TABLE 과 자료형 선택

CREATE TABLE 은 테이블 이름과 컬럼 목록, 각 컬럼의 자료형을 정의합니다. 컬럼이 여러 개인 CREATE TABLE 은 한 줄에 몰아 쓰지 않고, SELECT 절처럼 컬럼마다 줄을 나누고 앞에 쉼표를 붙여 한눈에 훑을 수 있게 씁니다.

CREATE TABLE dept (
    code  VARCHAR(10)
  , dname VARCHAR(20)
  , loc   VARCHAR(20)
  , CONSTRAINT pk_dept PRIMARY KEY (code)
);

자료형은 저장할 값의 성격에 맞춰 고릅니다. 정수는 INT, 문자열은 길이를 정하는 VARCHAR(n), 날짜는 DATE 처럼 범위와 정밀도를 자료형이 미리 정해 두면 잘못된 값이 애초에 못 들어옵니다. 길이를 짧게 잡으면 그 길이를 넘는 값은 INSERT 시점에 바로 거부되고, 3절 예제 2 에서 VARCHAR(5) 에 7자를 넣어 직접 확인합니다.

2.2 ALTER TABLE — 컬럼 추가 · 타입 변경 · 이름 변경

ALTER TABLE 은 이미 있는 테이블의 구조를 바꿉니다. 컬럼 추가는 ADD COLUMN, 자료형을 넓히는 변경과 컬럼 이름 변경은 DB 마다 문법이 다릅니다.

Oracle 은 타입 변경과 이름 변경이 완전히 분리돼 있습니다. MySQL 은 MODIFY 로 타입만, CHANGE COLUMN 으로 이름과 타입을 한 번에 바꿉니다. MSSQL 은 타입 변경만 ALTER COLUMN 이고, 이름 변경은 아예 다른 명령인 sp_rename 프로시저를 씁니다.

주의

EXEC sp_rename 은 저장 프로시저 호출이라 H2 가 지원하지 않습니다. 9절 예제에서 "함수를 찾을 수 없음" 오류로 직접 확인하고, 문법만 비교표로 정리합니다.

컬럼 폭을 넓히는 변경(VARCHAR(10) → VARCHAR(30))은 기존 값이 그대로 들어맞아 안전합니다. 반대로 폭을 좁히거나 자료형 자체를 바꾸는 변경은 기존 값이 새 정의에 안 맞으면 DB 가 거부하므로, 운영 테이블에서는 먼저 데이터를 점검한 뒤 바꿉니다.

2.3 DROP 과 TRUNCATE 의 차이

DROP TABLE 은 테이블 구조와 데이터를 모두 없앱니다. TRUNCATE TABLE 은 구조는 남기고 데이터만 한 번에 비웁니다. DELETE 로 전체 행을 지우는 것과 결과는 비슷하지만, TRUNCATE 는 행 단위로 지우지 않아 대체로 더 빠릅니다.

TRUNCATE 는 대부분의 DB 에서 롤백이 안 되는 명령입니다. MSSQL 은 예외로, 명시적 트랜잭션 안에서 실행한 TRUNCATE 는 커밋 전까지 롤백할 수 있습니다.

FK 로 참조되고 있는 테이블은 TRUNCATE 할 수 없습니다. 자식 테이블이 그 행을 가리키고 있는데 부모 데이터를 통째로 비우면 참조 무결성이 깨지기 때문입니다. 4절 예제 9 에서 이 오류를 직접 봅니다.

대용량 테이블을 복사하며 새로 만드는 CREATE TABLE ... AS SELECT(CTAS)는 이 레슨에서 다루지 않고, 대용량·배치 카테고리의 08 레슨에서 다룹니다.

2.4 제약 조건 이름 붙이기

제약 조건에 이름을 붙이지 않으면 DB 가 CONSTRAINT_A5F_0 같은 임의의 이름을 자동으로 만듭니다. 이런 이름은 나중에 오류 메시지나 DB 딕셔너리에서 어떤 제약인지 알아보기 어렵습니다.

CONSTRAINT pk_emp PRIMARY KEY (id)
CONSTRAINT uq_emp_email UNIQUE (email)
CONSTRAINT ck_emp_sal CHECK (sal >= 0)
CONSTRAINT fk_emp_dept FOREIGN KEY (dept) REFERENCES dept (code)

핵심

제약 조건은 테이블을 만들 때 이름을 직접 붙이는 습관을 들입니다. pk_테이블, uq_테이블_컬럼, fk_자식_부모 처럼 종류를 알 수 있는 접두사를 쓰면, 위반 오류가 났을 때 메시지만 보고도 어떤 규칙이 걸렸는지 바로 알 수 있습니다.

2.5 PK · UNIQUE · NOT NULL · CHECK · DEFAULT

다섯 가지 제약은 컬럼 하나하나가 지켜야 할 규칙을 정의합니다.

PRIMARY KEY 는 NOT NULL 과 UNIQUE 를 합친 것과 같아 그 컬럼은 항상 값이 있고 항상 유일합니다. UNIQUE 는 값이 있을 때만 중복을 막고, NULL 을 몇 개까지 허용하는지는 DB 마다 다릅니다.

Oracle·MySQL 은 UNIQUE 컬럼에 NULL 을 여러 행 허용합니다. NULL 은 "값이 없다"이지 "같은 값"이 아니라고 보기 때문입니다. MSSQL 은 UNIQUE 제약에서 NULL 도 하나의 비교 가능한 값으로 취급해 한 행만 허용합니다.

주의

UNIQUE 컬럼에 NULL 을 여러 번 넣을 수 있는지는 DB 마다 다릅니다. Oracle·MySQL 은 여러 개 허용하고 MSSQL 은 한 개만 허용합니다. 이 차이는 H2 도 MODE 별로 그대로 재현합니다(4절 예제 4·10). MSSQL 에서 굳이 NULL 도 유일해야 한다면 필터 인덱스(WHERE col IS NOT NULL)로 우회합니다.

CHECK 는 CHECK (sal >= 0) 처럼 컬럼 값이 조건식을 만족하는지 검사합니다. DEFAULT 는 INSERT 에서 값을 생략했을 때 채워질 기본값이며, 이미 있는 행에 ALTER TABLE ... ADD COLUMN ... DEFAULT 로 컬럼을 추가하면 기존 행에도 그 기본값이 즉시 채워집니다.

2.6 FK 와 ON DELETE CASCADE · SET NULL

FOREIGN KEY 는 한 테이블의 컬럼 값이 다른 테이블(부모)의 PK 나 UNIQUE 값 중 하나와 일치하도록 강제합니다. 부모에 없는 값을 자식에 넣으려 하면 거부되고, 자식이 참조 중인 부모 행을 그냥 지우려 해도 기본적으로 거부됩니다.

ON DELETE 절은 부모 행이 지워질 때 자식 행을 어떻게 할지 정합니다. CASCADE 는 자식 행도 함께 지우고, SET NULL 은 자식의 FK 컬럼을 NULL 로 바꿉니다. 아무 옵션도 없으면 자식이 남아 있는 한 부모 삭제 자체가 막힙니다.

Oracle·Tibero 는 ON UPDATE 절이 없어 부모 PK 를 바꿀 때 자식 값이 자동으로 따라 바뀌지 않습니다. 그래서 값이 바뀌지 않는 대리 키(surrogate key)를 PK 로 쓰는 것이 일반적입니다.

2.7 DDL 은 대부분 암묵적으로 커밋된다

Oracle 과 MySQL 은 DDL 문(CREATE·ALTER·DROP·TRUNCATE)을 실행하는 순간 바로 커밋되어 ROLLBACK 으로 되돌릴 수 없습니다. MSSQL 은 DDL 도 일반 DML 처럼 트랜잭션에 포함되어, 커밋 전이면 ROLLBACK 이 됩니다.

운영 DB 에서 ALTER TABLE·DROP TABLE 을 실행하기 전 백업·점검을 미리 끝내 두는 습관이 특히 Oracle·MySQL 에서 중요합니다. 이 동작은 트랜잭션 제어와 얽혀 있어 이 레슨의 H2 예제로는 재현하지 않고 개념만 짚습니다.