소스: sql-src/mid_08_ddl_constraints/02_constraints.sql, 03_dialects.sql. 여섯 가지 제약 조건 위반, FK 옵션별 동작, DB 별 DDL 문법 차이를 확인합니다.
CREATE TABLE emp (
id INT
, name VARCHAR(20) NOT NULL
, dept VARCHAR(10)
, email VARCHAR(30)
, sal INT DEFAULT 300
, 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) ON DELETE SET NULL
);
INSERT INTO emp (id, name, dept, email) VALUES (1, '김철수', '개발', 'kim@x.com');
SELECT id, name, sal FROM emp ORDER BY id;ID | NAME | SAL
---+--------+----
1 | 김철수 | 300
2 | 이영희 | 300
3 | 박민수 | 300
(3행)sal 을 INSERT 문에서 아예 빼고 이름·부서·이메일만 넣었더니 DEFAULT 300 이 자동으로 채워졌습니다. 하나의 CREATE TABLE 안에 다섯 종류의 제약(PK·NOT NULL·UNIQUE·CHECK·FK)이 모두 이름을 갖고 들어 있습니다.
-- @error
INSERT INTO emp (id, name, dept, email) VALUES (4, NULL, '개발', 'no-name@x.com');
-- @error
INSERT INTO emp (id, name, dept, email) VALUES (5, '최수정', '개발', 'kim@x.com');예상 오류: NULL not allowed for column "NAME"
예상 오류: Unique index or primary key violation: "PUBLIC.UQ_EMP_EMAIL_INDEX_1 ON PUBLIC.EMP(EMAIL NULLS FIRST) VALUES ( /* 1 */ 'kim@x.com' )"-- @error
INSERT INTO emp (id, name, dept, email, sal) VALUES (7, '오세훈', '개발', 'oh@x.com', -100);
-- @error
UPDATE emp SET sal = -100 WHERE id = 1;예상 오류: Check constraint violation: "CK_EMP_SAL: "세 오류 모두 이름 붙인 제약 덕분에 메시지에 UQ_EMP_EMAIL·CK_EMP_SAL 처럼 어떤 규칙이 걸렸는지가 드러납니다. CHECK 는 INSERT 뿐 아니라 UPDATE 로 값을 바꿀 때도 똑같이 검사되어, sal 을 -100 으로 고치려던 UPDATE 도 같은 오류로 거부됐습니다.
INSERT INTO emp (id, name, dept, email) VALUES (6, '정다운', '개발', NULL);
SELECT id, name, email FROM emp WHERE email IS NULL ORDER BY id;ID | NAME | EMAIL
---+--------+------
2 | 이영희 | NULL
3 | 박민수 | NULL
6 | 정다운 | NULL
(3행)email 은 UNIQUE 컬럼이지만 이영희·박민수·정다운 세 사람 모두 이메일이 NULL 인 채로 함께 들어갔습니다. UNIQUE 는 값이 실제로 중복될 때만 막고, NULL 끼리는 "같은 값"으로 보지 않기 때문입니다. 이 동작은 Oracle·MySQL 의 기본 동작과 같습니다.
DELETE FROM dept WHERE code = '개발';
SELECT id, name, dept FROM emp ORDER BY id;ID | NAME | DEPT
---+--------+-----
1 | 김철수 | NULL
2 | 이영희 | NULL
3 | 박민수 | NULL
6 | 정다운 | NULL
(4행)개발 부서를 지웠더니 그 부서를 가리키던 네 사원의 dept 가 전부 NULL 로 바뀌었습니다. fk_emp_dept 에 ON DELETE SET NULL 을 걸어 뒀기 때문입니다. 다른 테이블 쌍(p1·c1)에 ON DELETE CASCADE 를 걸면 부모를 지울 때 자식 행 자체가 함께 삭제됩니다.
CREATE TABLE c1 (
id INT
, pid INT
, CONSTRAINT pk_c1 PRIMARY KEY (id)
, CONSTRAINT fk_c1_p1 FOREIGN KEY (pid) REFERENCES p1 (id) ON DELETE CASCADE
);
INSERT INTO p1 VALUES (1);
INSERT INTO c1 VALUES (10, 1);
DELETE FROM p1 WHERE id = 1;
SELECT * FROM c1;ID | PID
---+----
(0행)p1 의 부모 행을 지우자 c1 의 자식 행도 함께 사라져 (0행)이 됐습니다. ON DELETE 옵션을 주지 않은 세 번째 쌍(p2·c2)은 자식이 남아 있는 채로 부모를 지우려 하면 그대로 거부됩니다.
-- @error
DELETE FROM p2 WHERE id = 1;예상 오류: Referential integrity constraint violation: "FK_C2_P2: PUBLIC.C2 FOREIGN KEY(PID) REFERENCES PUBLIC.P2(ID) (1)"같은 p2·c2 로 TRUNCATE 도 시도해 보면 마찬가지로 거부됩니다. DELETE 처럼 행 단위 참조 검사를 하지 않는 TRUNCATE 는, FK 로 참조되는 테이블이면 아예 대상에서 제외되기 때문입니다.
-- @error
TRUNCATE TABLE p2;예상 오류: Cannot truncate "PUBLIC.P2"SET MODE MSSQLServer;
CREATE TABLE t_ms (id INT PRIMARY KEY, email VARCHAR(20) UNIQUE);
INSERT INTO t_ms VALUES (1, NULL);
-- @error
INSERT INTO t_ms VALUES (2, NULL);예상 오류: Unique index or primary key violation: "PUBLIC.CONSTRAINT_INDEX_2 ON PUBLIC.T_MS(EMAIL NULLS FIRST) VALUES ( /* 1 */ NULL )"변형 3 의 Regular 모드와 달리, MSSQL 모드에서는 두 번째 NULL 이 그대로 거부됩니다. H2 는 이 MSSQL 고유의 UNIQUE·NULL 동작을 MODE 전환만으로 그대로 재현합니다.
SET MODE Oracle;
CREATE TABLE t_ora (
id NUMBER
, name VARCHAR2(10)
, hired DATE
, CONSTRAINT pk_t_ora PRIMARY KEY (id)
);
ALTER TABLE t_ora MODIFY (name VARCHAR2(30));
ALTER TABLE t_ora RENAME COLUMN hired TO join_date;| 자료형 | Oracle · Tibero | MySQL | MSSQL |
|---|---|---|---|
| 정수 | NUMBER | INT | INT |
| 문자열 | VARCHAR2(n) | VARCHAR(n) | NVARCHAR(n) |
| 날짜시간 | DATE | DATETIME | DATETIME2 |
한글처럼 2바이트 이상 문자가 섞이는 컬럼은 MSSQL 에서 NVARCHAR(n) 을 씁니다. VARCHAR 는 코드 페이지에 따라 한글이 깨질 수 있지만 NVARCHAR 는 유니코드를 고정 저장해 안전합니다.
주의Oracle 의 VARCHAR2(n) 은 기본적으로 n 을 바이트 기준으로 셉니다(NLS_LENGTH_SEMANTICS=BYTE). 한글은 UTF-8 에서 3바이트라 VARCHAR2(10) 에는 한글 3자 정도까지만 들어갈 수 있고, 문자 수로 정확히 맞추려면
VARCHAR2(10 CHAR)로 CHAR 단위를 명시합니다. H2 는 이 BYTE 기준을 재현하지 않고 항상 문자 수로 계산하므로(문법 검토만), 이 레슨의 실행 예제에서는 VARCHAR2(10) 에 한글 5자가 그대로 들어갑니다.
Oracle 은 빈 문자열도 NULL 로 저장합니다. NOT NULL 컬럼에 '' 를 넣으면 실제 Oracle 과 이 레슨의 H2(Oracle 모드) 모두 "NULL 은 허용되지 않음" 오류로 거부해 동작이 일치합니다.
SET MODE MySQL;
CREATE TABLE t_my (
id INT
, name VARCHAR(10)
, joined DATETIME
, CONSTRAINT pk_t_my PRIMARY KEY (id)
);
ALTER TABLE t_my MODIFY name VARCHAR(30);
ALTER TABLE t_my CHANGE COLUMN name emp_name VARCHAR(30);
ALTER TABLE t_my RENAME COLUMN emp_name TO full_name;MySQL 은 타입만 바꾸는 MODIFY, 이름과 타입을 함께 바꾸는 CHANGE COLUMN, 이름만 바꾸는 RENAME COLUMN(8.0부터) 세 가지를 상황에 맞게 씁니다. 셋 다 이 레슨의 H2(MySQL 모드)에서 그대로 실행됩니다.
주의MySQL 은 8.0.16 이전 버전에서 CHECK 절을 문법으로만 받아들이고 실제로는 적용하지 않았습니다. 이 레슨의 H2 는 MODE 와 상관없이 CHECK 를 항상 적용하므로(재현 안 됨, 문법 검토만),
INSERT INTO t_check VALUES (1, -100)이 H2 에서는 거부되지만 8.0.16 이전 실제 MySQL 이라면 그냥 들어갑니다. 배포 대상 MySQL 버전을 모른다면 CHECK 를 믿지 말고 애플리케이션 검증도 함께 둡니다.
SET MODE MSSQLServer;
CREATE TABLE t_ms (
id INT
, name NVARCHAR(10)
, joined DATETIME2
, CONSTRAINT pk_t_ms PRIMARY KEY (id)
);
ALTER TABLE t_ms ALTER COLUMN name NVARCHAR(30);
-- @error
EXEC sp_rename 't_ms.name', 'emp_name', 'COLUMN';예상 오류: Function "SP_RENAME" not found