SQL 완전 정리 ⑦ DDL·제약 조건·뷰·인덱스
앞 편들이 "이미 있는 테이블에서 꺼내는" DML 이었다면, 이 편은 테이블이라는 그릇 자체를 만들고 고치고 부수는 DDL(Data Definition Language)이다. 핵심은 세 가지다 — ① 열마다 타입으로 담을 수 있는 값의 모양을 정하고, ② 제약 조건으로 "들어와서는 안 되는 값"을 DBMS 가 대신 막게 하고, ③ 뷰·인덱스로 같은 데이터를 다르게 보여 주거나 빨리 찾게 한다. 제약 조건은 "검사 규칙", 뷰는 "저장된 SELECT", 인덱스는 "정렬된 찾아보기 표"라고 기억하면 각 문법의 동작이 거기서 그대로 따라 나온다. 본문의 쿼리·결과·오류 메시지는 PostgreSQL 14 와 SQLite 3.54 에서 실제로 실행해 맞췄다 (Oracle·MySQL 전용 문법은 대응만 소개).
0. 이 편에서 쓰는 테이블
부서
| dno | dname | loc |
|---|---|---|
| 10 | 개발 | 서울 |
| 20 | 영업 | 부산 |
| 30 | 인사 | 서울 |
| 40 | 기획 | NULL |
사원
| eno | ename | dno | job | sal | mgr | hiredate | bonus |
|---|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | NULL | 2019-03-01 | NULL |
| 2 | 이도윤 | 10 | 대리 | 350 | 1 | 2021-07-15 | 30 |
| 3 | 박서준 | 10 | 사원 | 280 | 2 | 2023-01-02 | NULL |
| 4 | 최지우 | 20 | 팀장 | 450 | NULL | 2018-11-20 | 50 |
| 5 | 정민재 | 20 | 사원 | 300 | 4 | 2022-05-09 | 20 |
| 6 | 한유나 | 20 | 사원 | 300 | 4 | 2024-02-01 | NULL |
| 7 | 오세린 | NULL | 사원 | 250 | NULL | 2025-01-06 | NULL |
이 편은 "테이블을 만드는" 편이라, 위 두 테이블을 제약 조건까지 붙여 직접 CREATE 하는 것부터 시작한다(§2). FK 참조 동작(§4)·뷰(§8)·인덱스(§9) 실험은 이 두 테이블의 복사본 위에서 한다.
1. 데이터 타입
열을 만들 때 반드시 타입을 정한다. 타입은 "이 열에 들어올 수 있는 값의 집합" — 곧 §5 에서 볼 도메인 무결성의 첫 관문이다. 실기에서 나오는 것은 문자·숫자·날짜 세 계열이면 충분하다.
1.1 문자 — CHAR vs VARCHAR
| 타입 | 길이 | 저장 방식 | 쓰임 |
|---|---|---|---|
CHAR(n) | 고정 n 자 | n 보다 짧으면 뒤를 공백으로 채워(padding) 저장 | 주민번호 앞자리, 코드값처럼 길이가 늘 같은 값 |
VARCHAR(n) | 가변, 최대 n 자 | 들어온 길이만큼만 저장 | 이름·주소처럼 길이가 들쭉날쭉한 값 |
CREATE TABLE t_char (c CHAR(5), v VARCHAR(5));
INSERT INTO t_char VALUES ('ab', 'ab');
SELECT LENGTH(c), OCTET_LENGTH(c), LENGTH(v) FROM t_char; -- PostgreSQL
| LENGTH(c) | OCTET_LENGTH(c) | LENGTH(v) |
|---|---|---|
| 2 | 5 | 2 |
c 는 실제로 'ab '(5바이트)로 저장돼 있지만, PostgreSQL 은 CHAR 의 뒤 공백을 "의미 없는 채움"으로 보고
LENGTH·문자열 연결에서 떼어 낸다. 비교에서도 차이가 난다.
SELECT 'ab'::char(5) = 'ab '::char(5) AS c_eq,
'ab'::varchar(5) = 'ab '::varchar(5) AS v_eq;
| c_eq | v_eq |
|---|---|
| t | f |
CHAR 끼리 비교할 때는 뒤 공백을 무시하지만(둘 다 같은 길이로 채워지므로), VARCHAR 의 공백은 진짜 문자다.
길이를 넘으면 오류가 난다(표준 동작, PostgreSQL·Oracle·MySQL strict 모드).
INSERT INTO t_char VALUES ('abcdef', 'x');
-- ERROR: value too long for type character(5)
SQLite 는 예외 — SQLite 는
CHAR(5)·VARCHAR(5)를 그냥 TEXT 로 취급해 길이 제한도 패딩도 하지 않는다.VARCHAR(5)열에'abcdefgh'를 넣어도 들어가고LENGTH는 8 이다. 타입 검사 실험은 PostgreSQL 기준으로 보자.
1.2 숫자 — INTEGER / NUMERIC(p, s) / DECIMAL
| 타입 | 의미 | 예 |
|---|---|---|
SMALLINT·INTEGER(INT)·BIGINT | 정수. 범위만 다르다(2·4·8바이트) | 사번, 수량 |
NUMERIC(p, s) = DECIMAL(p, s) | 고정 소수점. 전체 자릿수 p(정밀도), 그중 소수 이하 s(스케일) | 금액, 이율 |
REAL·DOUBLE PRECISION·FLOAT | 부동 소수점(근삿값) | 과학 측정치 |
NUMERIC(5,2) 는 "전체 5자리, 소수 2자리" → 정수부는 최대 3자리, 범위 −999.99 ~ 999.99.
SELECT CAST(123.456 AS NUMERIC(5,2)); -- 123.46 (소수 3째 자리에서 반올림)
SELECT CAST(1234.5 AS NUMERIC(5,2));
-- ERROR: numeric field overflow
-- DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3.
함정 — 소수부가 넘치면 반올림해서 넣어 주지만, 정수부가 넘치면 오류다. "p 는 소수점 이하 포함 전체 자릿수"를 잊고
NUMERIC(5,2)에 1234.5 가 들어간다고 답하는 실수가 흔하다.
정수 타입끼리의 나눗셈은 정수로 잘린다(1편에서 본 7/2 = 3). 금액 계산에 NUMERIC 을 쓰는 이유는 FLOAT 의
이진 근삿값 오차(0.1 + 0.2 ≠ 0.3)가 없기 때문이다.
1.3 날짜·시간 — DATE / TIME / TIMESTAMP
| 타입 | 담는 것 | 리터럴 |
|---|---|---|
DATE | 연-월-일 (PostgreSQL·MySQL). Oracle DATE 는 시·분·초까지 포함 | DATE '2024-02-28' |
TIME | 시:분:초 | TIME '09:30:00' |
TIMESTAMP | 날짜 + 시각 (소수 초까지) | TIMESTAMP '2024-02-28 23:30:00' |
SELECT DATE '2024-02-28' + 1 AS d1,
TIMESTAMP '2024-02-28 23:30:00' + INTERVAL '1 hour' AS ts; -- PostgreSQL
| d1 | ts |
|---|---|
| 2024-02-29 | 2024-02-29 00:30:00 |
윤년(2024-02-29)과 자정 넘김을 DBMS 가 알아서 계산한다 — 날짜를 문자열로 저장하지 말아야 하는 이유다.
1.4 DBMS 별 대응
| 표준 / PostgreSQL | Oracle | MySQL | 비고 |
|---|---|---|---|
VARCHAR(n) | VARCHAR2(n) | VARCHAR(n) | Oracle 은 VARCHAR2 사용 권장. Oracle 은 빈 문자열 '' 을 NULL 로 취급 |
CHAR(n) | CHAR(n) | CHAR(n) | |
INTEGER, NUMERIC(p,s) | NUMBER(p), NUMBER(p,s) | INT, DECIMAL(p,s) | Oracle 은 정수·실수 모두 NUMBER 하나로 |
TEXT | CLOB | TEXT | 긴 문자열 |
DATE / TIMESTAMP | DATE(초까지) / TIMESTAMP | DATE / DATETIME·TIMESTAMP | |
BOOLEAN | (없음 — 23ai 이전) NUMBER(1)·CHAR(1) | BOOLEAN(=TINYINT(1)) |
2. CREATE TABLE — 전체 골격
2.1 문법 골격
CREATE TABLE 테이블명 (
열이름 데이터타입 [DEFAULT 기본값] [열 수준 제약 …],
열이름 데이터타입 …,
…
[CONSTRAINT 제약이름] 테이블 수준 제약 (열목록),
…
);
한 테이블 정의 안에는 열 정의와 테이블 수준 제약이 쉼표로 나열된다. 제약은 두 자리에 쓸 수 있다.
| 위치 | 모양 | 쓸 수 있는 것 |
|---|---|---|
| 열 수준 | 열 정의 바로 뒤에 NOT NULL, UNIQUE, PRIMARY KEY, CHECK (…), REFERENCES 부모(열) | 그 열 하나에만 관한 제약 |
| 테이블 수준 | 열 정의가 끝난 뒤 따로 PRIMARY KEY (a, b), FOREIGN KEY (a) REFERENCES …, CHECK (a <= b) | 여러 열에 걸친 제약(복합 키, 열끼리 비교하는 CHECK) |
NOT NULL 은 열 수준에만 쓴다(표준). 복합 기본키
PRIMARY KEY (eno, pno)나CHECK (bonus <= sal)처럼 두 열 이상이 얽힌 제약은 테이블 수준에만 쓸 수 있다.
2.2 공통 스키마를 제약과 함께 만들기
CREATE TABLE 부서 (
dno INTEGER PRIMARY KEY, -- 열 수준
dname VARCHAR(20) NOT NULL UNIQUE,
loc VARCHAR(20)
);
CREATE TABLE 사원 (
eno INTEGER NOT NULL,
ename VARCHAR(20) NOT NULL,
dno INTEGER,
job VARCHAR(10) DEFAULT '사원',
sal NUMERIC(7,0) CHECK (sal > 0), -- 이름 없는 열 수준 CHECK
mgr INTEGER,
hiredate DATE DEFAULT CURRENT_DATE,
bonus NUMERIC(5,0),
CONSTRAINT pk_사원 PRIMARY KEY (eno), -- 테이블 수준
CONSTRAINT fk_사원_부서 FOREIGN KEY (dno) REFERENCES 부서 (dno),
CONSTRAINT fk_사원_mgr FOREIGN KEY (mgr) REFERENCES 사원 (eno), -- 자기 참조
CONSTRAINT ck_bonus CHECK (bonus <= sal) -- 두 열 비교
);
부서 4행, 사원 7행을 §0 의 값 그대로 INSERT 하면 모든 제약을 통과한다. fk_사원_mgr 는 자기 자신을 참조하는 FK —
관리자도 사원이므로 mgr 은 사원.eno 에 있는 값(또는 NULL)이어야 한다.
2.3 CONSTRAINT 이름 붙이기
CONSTRAINT 이름 을 생략하면 DBMS 가 이름을 지어 준다.
| 제약 | 직접 지은 이름 | PostgreSQL 이 자동으로 지은 이름 |
|---|---|---|
| 사원 PK | pk_사원 | (생략 시) 사원_pkey |
| sal CHECK | — | 사원_sal_check |
| 부서.dname UNIQUE | — | 부서_dname_key |
Oracle 은 SYS_C0012345 같은 일련번호를 붙인다. 이름이 중요한 이유는 두 가지다 — ① 위반 오류 메시지에 이름이 그대로 찍혀
어떤 규칙이 깨졌는지 바로 알 수 있고, ② 나중에 ALTER TABLE … DROP CONSTRAINT 이름 으로 지울 때 이름이 필요하다(§6).
3. 제약 조건 6종
| 제약 | 막는 것 | NULL | 테이블당 | 자동 인덱스 |
|---|---|---|---|---|
NOT NULL | 값 비우기 | 금지 | 열마다 | — |
UNIQUE | 중복 값 | 여러 개 허용(대부분 DBMS) | 여러 개 | 생성 |
PRIMARY KEY | 중복 + NULL | 금지 | 하나 | 생성 |
FOREIGN KEY | 부모에 없는 값 | 허용 | 여러 개 | (PG·Oracle 은 자식 쪽 자동 생성 안 함) |
CHECK | 조건이 FALSE 인 값 | UNKNOWN 이면 통과 | 여러 개 | — |
DEFAULT | (막지 않음) 값 생략 시 채움 | — | 열마다 | — |
DEFAULT 는 엄밀히 "제약"이라기보다 열 속성이지만, 교재·시험에서는 제약 조건 목록에 함께 넣는다.
3.1 NOT NULL·DEFAULT
INSERT INTO 사원 (eno, ename, sal) VALUES (8, '신입', 200);
SELECT eno, ename, job, dno FROM 사원 WHERE eno = 8;
| eno | ename | job | dno |
|---|---|---|---|
| 8 | 신입 | 사원 | NULL |
열 목록에서 빠진 열만 DEFAULT 를 받는다 — job 은 '사원', hiredate 는 오늘 날짜, DEFAULT 가 없는 dno 는 NULL.
반대로 NULL 을 명시하면 DEFAULT 가 끼어들지 않는다.
INSERT INTO 사원 (eno, ename, job, sal) VALUES (9, '명시', NULL, 200);
SELECT eno, job FROM 사원 WHERE eno = 9; -- job = NULL (DEFAULT '사원' 아님)
함정 — "DEFAULT 가 있으면 NULL 이 안 들어간다"는 틀렸다. DEFAULT 는 생략했을 때만 쓰인다. NULL 을 막으려면 NOT NULL 이 따로 필요하다. 명시적으로 기본값을 쓰고 싶으면
VALUES (9, '명시', DEFAULT, 200)처럼DEFAULT키워드를 넣는다.
3.2 위반하면 — 오류 예시 (PostgreSQL)
| 시도 | 오류 메시지 |
|---|---|
INSERT … (eno, ename, sal) VALUES (1, '중복', 100) | duplicate key value violates unique constraint "pk_사원" — Key (eno)=(1) already exists. |
… VALUES (NULL, '널키', 100) | null value in column "eno" of relation "사원" violates not-null constraint |
… VALUES (10, NULL, 100) | null value in column "ename" … violates not-null constraint |
… VALUES (10, '음수', -5) | new row for relation "사원" violates check constraint "사원_sal_check" |
… (eno, ename, dno, sal) VALUES (10, '유령', 99, 100) | violates foreign key constraint "fk_사원_부서" — Key (dno)=(99) is not present in table "부서". |
INSERT INTO 부서 VALUES (50, '개발', '대전') | duplicate key value violates unique constraint "부서_dname_key" |
… (eno, ename, sal, bonus) VALUES (10, '보너스', 100, 200) | violates check constraint "ck_bonus" |
DELETE FROM 부서 WHERE dno = 10 | update or delete on table "부서" violates foreign key constraint "fk_사원_부서" on table "사원" |
위반한 문장은 통째로 실패하고 테이블은 바뀌지 않는다. 마지막 줄은 부모 쪽 위반이다 — 개발부에 사원이 있으므로 부서 10 을 지우면 자식 행이 고아가 된다. 이때 무슨 일이 일어날지 정하는 것이 §4 의 참조 동작이다.
3.3 PRIMARY KEY = UNIQUE + NOT NULL (+ 하나뿐)
기본키는 행을 유일하게 식별하는 열(들)이다. 그래서 중복도 NULL 도 안 된다. 기본키 후보였지만 뽑히지 못한 키
(대체키)는 보통 UNIQUE NOT NULL 로 건다 — 위 부서.dname 이 그 예다.
복합 기본키는 테이블 수준으로 쓴다.
CREATE TABLE 참여 (
eno INTEGER REFERENCES 사원 (eno),
pno INTEGER,
hours INTEGER,
PRIMARY KEY (eno, pno) -- (2,100)·(2,200) 은 OK, (2,100) 두 번은 위반
);
3.4 UNIQUE 는 NULL 을 여러 개 허용한다
CREATE TABLE u (k INTEGER UNIQUE);
INSERT INTO u VALUES (NULL), (NULL), (1); -- 성공
SELECT COUNT(*) AS rows, COUNT(k) AS nonnull FROM u;
| rows | nonnull |
|---|---|
| 3 | 1 |
INSERT INTO u VALUES (1) 을 한 번 더 하면 그때는 duplicate key … "u_k_key" 로 실패한다. PostgreSQL·SQLite 모두 같은 결과다.
왜 그런가? UNIQUE 는 "두 행의 값이 같으면 안 된다"는 규칙인데, NULL = NULL 은 TRUE 가 아니라 UNKNOWN 이다
(1편의 3값 논리). "같다"가 성립하지 않으니 위반도 아니다.
| DBMS | UNIQUE 열에 NULL 여러 개 |
|---|---|
| PostgreSQL·Oracle·MySQL·SQLite | 허용 |
| SQL Server | 하나만 허용 (두 번째 NULL 은 중복 오류) |
| PostgreSQL 15+ | UNIQUE NULLS NOT DISTINCT 로 하나만 허용하게 바꿀 수 있음 |
3.5 CHECK 는 UNKNOWN 이면 통과한다
INSERT INTO 사원 (eno, ename, sal, bonus) VALUES (11, '널급여', NULL, 999); -- 성공!
| eno | ename | sal | bonus |
|---|---|---|---|
| 11 | 널급여 | NULL | 999 |
CHECK (sal > 0) 은 NULL > 0 → UNKNOWN, CHECK (bonus <= sal) 도 999 <= NULL → UNKNOWN. CHECK 는 결과가 FALSE 일 때만
거부하므로 둘 다 통과한다. WHERE 가 "TRUE 인 행만 남기는" 것과 정반대 방향이라 헷갈리기 쉽다.
| 조건 결과 | WHERE | CHECK |
|---|---|---|
| TRUE | 남김 | 통과 |
| FALSE | 버림 | 거부 |
| UNKNOWN | 버림 | 통과 |
NULL 까지 막으려면 sal NUMERIC(7,0) NOT NULL CHECK (sal > 0) 처럼 NOT NULL 을 함께 건다.
3.6 FOREIGN KEY
[CONSTRAINT 이름] FOREIGN KEY (자식열 [, …])
REFERENCES 부모테이블 (부모열 [, …])
[ON DELETE 동작] [ON UPDATE 동작]
- 참조되는 부모 열은 PRIMARY KEY 또는 UNIQUE 여야 한다(그래야 "어느 행"인지 하나로 정해진다).
- 자식 열 값은 부모에 있는 값이거나 NULL. NULL 은 "아직 소속 없음"을 뜻하며 검사하지 않는다 — 그래서 오세린(dno NULL)이 들어간다.
- 열 수준으로는
dno INTEGER REFERENCES 부서(dno)처럼FOREIGN KEY키워드 없이 쓴다.
SQLite 주의 — SQLite 는 FK 를 문법으로는 받지만 기본값이 꺼져 있다.
PRAGMA foreign_keys = ON;을 실행해야 비로소FOREIGN KEY constraint failed가 난다. 꺼진 상태에서는 부모에 없는 99 도 그냥 들어간다.
4. FK 참조 동작 — ON DELETE / ON UPDATE
부모 행을 지우거나 키를 바꿀 때 그 값을 참조하던 자식 행을 어떻게 할지 정하는 옵션이다.
FOREIGN KEY (dno) REFERENCES 부서 (dno)
ON DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }
ON UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }
| 동작 | 부모 삭제 시 자식 행은 | 한 줄 의미 |
|---|---|---|
CASCADE | 같이 삭제 (UPDATE 면 같이 변경) | 연쇄 |
SET NULL | FK 열을 NULL 로 | 소속 해제 |
SET DEFAULT | FK 열을 DEFAULT 값으로 | 기본 소속으로 이동 |
RESTRICT | 자식이 있으면 부모 삭제 즉시 거부 | 금지 |
NO ACTION | 자식이 있으면 거부 — 단 문장(또는 트랜잭션) 끝에 검사 | 금지(기본값) |
4.1 같은 데이터로 다섯 가지 비교
CREATE TABLE d2 (dno INTEGER PRIMARY KEY, dname VARCHAR(10));
INSERT INTO d2 VALUES (10,'개발'), (20,'영업'), (30,'인사'), (99,'미배정');
CREATE TABLE e2 (
eno INTEGER PRIMARY KEY,
ename VARCHAR(10),
dno INTEGER DEFAULT 99 REFERENCES d2 (dno) ON DELETE ⟨동작⟩
);
INSERT INTO e2 VALUES (1,'김하늘',10), (2,'이도윤',10), (4,'최지우',20);
DELETE FROM d2 WHERE dno = 10; -- 자식 2명을 가진 부모 삭제
| ON DELETE | DELETE 결과 | e2 (eno:dno) | d2 의 dno |
|---|---|---|---|
CASCADE | 성공 | 4:20 (1·2번 사원 삭제됨) | 20, 30, 99 |
SET NULL | 성공 | 1:NULL, 2:NULL, 4:20 | 20, 30, 99 |
SET DEFAULT | 성공 | 1:99, 2:99, 4:20 | 20, 30, 99 |
RESTRICT | 오류 | 1:10, 2:10, 4:20 (그대로) | 10, 20, 30, 99 |
NO ACTION | 오류 | 1:10, 2:10, 4:20 (그대로) | 10, 20, 30, 99 |
PostgreSQL·SQLite(PRAGMA foreign_keys=ON) 모두 같은 결과다. 자식이 없는 DELETE FROM d2 WHERE dno = 30 은 다섯 경우 모두 그냥 성공한다.
SET DEFAULT 함정 — 기본값 99 가 부모에 실제로 있어야 한다. 99(미배정) 행이 없으면 바뀐 값이 다시 FK 를 위반해 DELETE 가 실패한다. SET NULL 도 FK 열이
NOT NULL이면 쓸 수 없다.
ON UPDATE 도 같은 방식이다. ON UPDATE CASCADE 로 걸고 UPDATE d2 SET dno = 11 WHERE dno = 10 을 하면 자식 두 행의 dno 도 11 로 따라 바뀐다(PG 확인).
| DBMS | ON DELETE 지원 | ON UPDATE 지원 |
|---|---|---|
| PostgreSQL·MySQL(InnoDB)·SQLite | 5종 모두 (MySQL InnoDB 는 SET DEFAULT 거부) | 5종 |
| Oracle | CASCADE, SET NULL 만 (생략 = NO ACTION) | 없음 |
4.2 RESTRICT vs NO ACTION — 검사 시점
결과만 보면 둘 다 "거부"인데 무엇이 다른가? 언제 검사하느냐다.
RESTRICT— 부모 행을 지우는 그 순간 자식을 확인하고 거부. 미룰 수 없다.NO ACTION— 문장이 끝날 때 확인한다. 제약을DEFERRABLE INITIALLY DEFERRED로 만들면 COMMIT 시점까지 미룰 수 있어, 그 사이에 부모를 되살리면 통과한다.
CREATE TABLE e2 (eno INTEGER PRIMARY KEY,
dno INTEGER REFERENCES d2 (dno) ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED);
INSERT INTO e2 VALUES (1, 10);
BEGIN;
DELETE FROM d2 WHERE dno = 10; -- 아직 오류 아님 (검사 보류)
INSERT INTO d2 VALUES (10, '개발2'); -- 부모를 다시 만들어 둔다
COMMIT; -- 커밋 시 검사 → 자식의 10 이 부모에 있으므로 통과
같은 코드를 ON DELETE RESTRICT 로 바꾸면 DEFERRABLE 을 붙였어도 DELETE 줄에서 바로 오류가 나고 트랜잭션 전체가 실패한다(PG 확인).
DEFERRABLE 이 없는 보통의 경우에는 두 동작의 차이가 겉으로 드러나지 않는다.
5. 무결성 3종
제약 조건은 결국 무결성(integrity) — "데이터가 현실과 어긋나지 않는 상태" — 을 DBMS 가 지키게 하는 장치다.
| 무결성 | 규칙 | 지키는 제약 | 깨진 예 |
|---|---|---|---|
| 개체 무결성 | 기본키는 NULL 일 수 없고 중복될 수 없다 | PRIMARY KEY | eno 가 NULL 인 사원 → 누구인지 식별 불가 |
| 참조 무결성 | 외래키 값은 부모의 기본키 값이거나 NULL 이다 | FOREIGN KEY (+ 참조 동작) | dno = 99 인 사원 → 존재하지 않는 부서 소속 |
| 도메인 무결성 | 속성 값은 정의된 도메인(타입·범위·형식) 안에 있어야 한다 | 데이터 타입, CHECK, NOT NULL, DEFAULT | sal = −5, 성별 = 'X' |
교재에 따라 키 무결성(후보키는 유일해야 한다 →
UNIQUE), 사용자 정의 무결성(업무 규칙 →CHECK·트리거)을 더하기도 한다. 실기에서 "기본키에 NULL 불가"는 개체, "외래키는 참조 테이블의 기본키 값 또는 NULL"은 참조 무결성이다.
6. ALTER TABLE — 이미 있는 테이블 고치기
데이터를 지우지 않고 구조만 바꾼다. 실험용 테이블 하나로 차례대로 해 보자(PostgreSQL).
CREATE TABLE t (id INTEGER PRIMARY KEY, name VARCHAR(10), sal INTEGER);
INSERT INTO t VALUES (1,'a',100), (2,'b',200);
ALTER TABLE t ADD COLUMN email VARCHAR(30) DEFAULT 'none'; -- ① 열 추가
| id | name | sal | |
|---|---|---|---|
| 1 | a | 100 | none |
| 2 | b | 200 | none |
새 열은 맨 뒤에 붙고, 기존 행에는 DEFAULT 값(없으면 NULL)이 채워진다. 그래서 기존 행이 있는 테이블에
ADD COLUMN x INTEGER NOT NULL 을 DEFAULT 없이 하면 기존 행이 NULL 이 되어 실패한다.
ALTER TABLE t ALTER COLUMN name TYPE VARCHAR(30); -- ② 타입 변경
ALTER TABLE t ALTER COLUMN name SET NOT NULL; -- ③ NOT NULL 추가
ALTER TABLE t RENAME COLUMN name TO ename; -- ④ 열 이름 변경
ALTER TABLE t ADD CONSTRAINT ck_t_sal CHECK (sal >= 150); -- ⑤ 제약 추가 → 실패!
-- ERROR: check constraint "ck_t_sal" of relation "t" is violated by some row
ALTER TABLE t ADD CONSTRAINT ck_t_sal2 CHECK (sal >= 50); -- 성공
ALTER TABLE t DROP CONSTRAINT ck_t_sal2; -- ⑥ 제약 삭제 (이름 필요!)
ALTER TABLE t DROP COLUMN email; -- ⑦ 열 삭제
ALTER TABLE t RENAME TO t_new; -- ⑧ 테이블 이름 변경
⑤ 처럼 이미 들어 있는 데이터가 새 제약을 어기면 제약 추가 자체가 실패한다(id 1 의 sal 100). 타입을 줄일 때도 같다 —
ALTER COLUMN sal TYPE VARCHAR(2) 는 '100' 이 2자를 넘어 value too long 으로 실패한다.
6.1 DBMS 별 문법 대응
| 작업 | 표준 / PostgreSQL | Oracle | MySQL |
|---|---|---|---|
| 열 추가 | ADD [COLUMN] c 타입 | ADD (c 타입) | ADD [COLUMN] c 타입 |
| 열 삭제 | DROP [COLUMN] c | DROP COLUMN c | DROP [COLUMN] c |
| 타입 변경 | ALTER COLUMN c TYPE 타입 | MODIFY (c 타입) | MODIFY [COLUMN] c 타입 |
| 열 이름 변경 | RENAME COLUMN a TO b | RENAME COLUMN a TO b | RENAME COLUMN a TO b (8.0+) / CHANGE a b 타입 |
| NOT NULL | ALTER COLUMN c SET NOT NULL | MODIFY (c NOT NULL) | MODIFY c 타입 NOT NULL |
| 기본값 | ALTER COLUMN c SET DEFAULT v | MODIFY (c DEFAULT v) | ALTER [COLUMN] c SET DEFAULT v |
| 제약 추가 | ADD CONSTRAINT 이름 … | 같음 | 같음 |
| 제약 삭제 | DROP CONSTRAINT 이름 | 같음 | DROP CONSTRAINT 이름(8.0.19+), DROP FOREIGN KEY 이름, DROP PRIMARY KEY |
| 테이블 이름 | ALTER TABLE a RENAME TO b | 같음 / RENAME a TO b | RENAME TABLE a TO b |
실기 빈칸 문제의 단골은 Oracle·MySQL 의
MODIFY와 표준의ALTER COLUMN구분이다.ALTER TABLE 사원 MODIFY ename VARCHAR(40);은 MySQL,ALTER TABLE 사원 ALTER COLUMN ename TYPE VARCHAR(40);은 PostgreSQL 이다. SQLite 의 ALTER TABLE 은ADD COLUMN·DROP COLUMN·RENAME만 되고 타입 변경·제약 추가는 안 된다.
7. DROP·TRUNCATE·DELETE, 그리고 CREATE TABLE AS
7.1 세 가지 "지우기" 비교
DELETE FROM t [WHERE …] | TRUNCATE TABLE t | DROP TABLE t | |
|---|---|---|---|
| 분류 | DML | DDL | DDL |
| 지우는 것 | 조건에 맞는 행 | 모든 행 | 행 + 테이블 구조 자체(제약·인덱스·권한 포함) |
| WHERE | 가능 | 불가 | 불가 |
| 구조 유지 | 유지 | 유지 | 사라짐 |
| 행 단위 DELETE 트리거 | 발생 | 발생 안 함 | 발생 안 함 |
| 로그·속도 | 행마다 기록 → 느림 | 저장 공간을 통째로 비움 → 빠름 | 빠름 |
| 롤백 (Oracle·MySQL) | 가능 | 불가(자동 커밋) | 불가(자동 커밋) |
| 롤백 (PostgreSQL) | 가능 | 가능(트랜잭션 안이면) | 가능(트랜잭션 안이면) |
PostgreSQL 은 DDL 도 트랜잭션의 일부라 되돌릴 수 있다. 실제로 확인한 순서:
-- s 에 3행, AFTER DELETE FOR EACH ROW 트리거가 log 테이블에 'del id' 를 남긴다
BEGIN; TRUNCATE s; SELECT COUNT(*) FROM s; ROLLBACK; -- 0 → 롤백 후 3
BEGIN; DROP TABLE s; ROLLBACK; -- 롤백 후 테이블·데이터 그대로
DELETE FROM s WHERE id = 1; -- 트리거 발생 → log: 'del 1'
TRUNCATE s; -- 남은 2행 삭제, 트리거 없음 → log 는 여전히 'del 1' 하나
시험 기준 — 정보처리기사는 Oracle 관점으로 "TRUNCATE·DROP 은 DDL 이라 롤백할 수 없다, DELETE 는 롤백 가능"을 정답으로 본다. Oracle·MySQL 은 DDL 을 실행하기 전후로 암묵적 COMMIT 을 하기 때문이다. PostgreSQL 은 예외로 기억해 두자. SQLite 에는
TRUNCATE문이 없다(DELETE FROM t;를 내부적으로 빠르게 처리한다).
7.2 DROP … CASCADE / RESTRICT
CREATE VIEW v_s AS SELECT id FROM s;
DROP TABLE s; -- 또는 DROP TABLE s RESTRICT;
-- ERROR: cannot drop table s because other objects depend on it
-- DETAIL: view v_s depends on table s
-- HINT: Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE s CASCADE;
-- NOTICE: drop cascades to view v_s ← 뷰도 함께 삭제됨
| 옵션 | 의미 | 기본값 |
|---|---|---|
RESTRICT | 이 객체에 의존하는 객체(뷰·FK 등)가 있으면 삭제 거부 | PostgreSQL·표준의 기본 |
CASCADE | 의존 객체까지 함께 삭제(FK 는 제약만 삭제, 자식 테이블은 남음) |
Oracle 은 DROP TABLE 부서 CASCADE CONSTRAINTS; — 참조하는 FK 제약을 같이 지운다. Oracle 에서 의존 뷰는 지워지지 않고 INVALID 상태로 남는다.
같은 단어
CASCADE가 두 곳에 나온다.ON DELETE CASCADE는 행(데이터)을 연쇄 삭제하고,DROP … CASCADE는 객체(뷰·제약)를 연쇄 삭제한다.
7.3 CREATE TABLE … AS SELECT (CTAS)
CREATE TABLE s_copy AS SELECT * FROM s WHERE v > 10; -- 구조 + 데이터 복사
CREATE TABLE s_empty AS SELECT * FROM s WHERE 1 = 0; -- 구조만 복사 (0행)
원본 s 는 id INTEGER PRIMARY KEY, v INTEGER NOT NULL DEFAULT 1 CHECK (v > 0) 였다. 복사본의 열 정보를 데이터 사전(§11)에서 보면:
| table | column | data_type | is_nullable | column_default |
|---|---|---|---|---|
| s | id | integer | NO | |
| s | v | integer | NO | 1 |
| s_copy | id | integer | YES | |
| s_copy | v | integer | YES |
열 이름과 타입만 복사되고 PK·NOT NULL·CHECK·DEFAULT·FK·인덱스는 모두 빠진다. 그래서 INSERT INTO s_copy VALUES (2, -5), (2, NULL) 이 성공한다
(id 중복, 음수, NULL 모두 통과). SQLite 도 CREATE TABLE c(id INT, v INT) 로 제약 없이 만든다.
Oracle 은 예외적으로 NOT NULL 만 복사한다. 시험에서 "CTAS 로 복사하면 제약 조건은 복사되지 않는다(Oracle 은 NOT NULL 제외)"로 나온다.
WHERE 1 = 0은 "항상 거짓" 조건으로 빈 껍데기 테이블을 만드는 관용구다.
8. VIEW — 이름 붙인 SELECT
뷰는 데이터를 저장하지 않는 가상 테이블이다. DBMS 는 뷰의 정의(SELECT 문)만 데이터 사전에 저장해 두고, 누가 뷰를 조회하면 그 SELECT 를 그 자리에서 펼쳐(치환해) 실행한다. 그래서 뷰의 동작은 모두 "뷰 이름 = 괄호 친 서브쿼리"로 이해하면 된다.
8.1 생성·변경·삭제
CREATE [OR REPLACE] VIEW 뷰이름 [(열별칭, …)] AS
SELECT …
[WITH CHECK OPTION];
DROP VIEW 뷰이름 [CASCADE | RESTRICT];
CREATE VIEW v_dev AS
SELECT eno, ename, sal FROM 사원 WHERE dno = 10;
SELECT * FROM v_dev ORDER BY eno;
| eno | ename | sal |
|---|---|---|
| 1 | 김하늘 | 500 |
| 2 | 이도윤 | 350 |
| 3 | 박서준 | 280 |
CREATE OR REPLACE VIEW— 있으면 정의를 바꾸고 없으면 만든다. 뷰를 지웠다 다시 만들면 권한·의존 객체가 날아가므로 이 방식을 쓴다. PostgreSQL 은 열을 뒤에 추가하는 변경만 허용한다(eno, ename, sal, job은 OK,eno, ename으로 줄이면cannot drop columns from view).- 표준 SQL 에는
ALTER VIEW로 정의를 바꾸는 문법이 없다 — 정의 변경은 OR REPLACE 또는 DROP 후 CREATE. (ALTER VIEW … RENAME같은 부가 기능은 벤더별로 있다.)
8.2 기반 테이블 변경이 즉시 보인다
UPDATE 사원 SET sal = 600 WHERE eno = 1;
SELECT * FROM v_dev WHERE eno = 1; -- 김하늘 | 600
뷰가 결과를 저장하지 않으니 조회할 때마다 최신 데이터가 나온다. (결과를 실제로 저장해 두는 것은 구체화 뷰(materialized view)로, REFRESH 해야 갱신된다 — 일반 뷰와 구분.)
8.3 뷰를 통한 갱신
단순한 뷰는 뷰에 UPDATE·INSERT 하면 기반 테이블이 바뀐다.
UPDATE v_dev SET sal = 290 WHERE eno = 3;
SELECT eno, sal FROM 사원 WHERE eno = 3; -- 3 | 290
갱신 가능한 뷰의 조건 (표준·PostgreSQL 자동 갱신 기준) — 뷰의 한 행이 기반 테이블의 정확히 한 행에 대응해야 한다.
| 갱신 불가 요인 | 이유 |
|---|---|
GROUP BY·HAVING·집계 함수 | 한 행 = 여러 원본 행의 요약 → 어느 행을 고칠지 모름 |
DISTINCT | 중복 제거로 원본 행과 1:1 이 깨짐 |
| 조인(FROM 에 테이블 둘 이상) | 어느 테이블을 고칠지 모호 (DBMS 별로 일부 허용) |
UNION 등 집합 연산 | 행의 출처가 여럿 |
계산식·상수 열 (sal * 12) | 그 열은 값을 거꾸로 되돌릴 수 없음 (다른 열은 갱신 가능) |
INSERT 시 뷰에 없는 열이 NOT NULL(DEFAULT 없음) | 그 열을 채울 방법이 없음 |
CREATE VIEW v_dept_sum AS
SELECT dno, COUNT(*) AS cnt, SUM(sal) AS total FROM 사원 GROUP BY dno;
UPDATE v_dept_sum SET total = 0 WHERE dno = 10;
-- ERROR: cannot update view "v_dept_sum"
-- DETAIL: Views containing GROUP BY are not automatically updatable.
SQLite 의 뷰는 전부 읽기 전용이다(
cannot modify v because it is a view). INSTEAD OF 트리거를 달아야 갱신된다.
8.4 WITH CHECK OPTION
뷰의 WHERE 를 벗어나는 행을 뷰를 통해 넣으면 어떻게 될까?
INSERT INTO v_dev VALUES (20, '외부', 100); -- 성공 (dno 는 뷰에 없으니 NULL)
SELECT eno, ename, dno FROM 사원 WHERE eno = 20; -- 20 | 외부 | NULL
SELECT * FROM v_dev WHERE eno = 20; -- 0행!
뷰로 넣은 행이 뷰에서 바로 사라진다(dno 가 10 이 아니므로). 이런 "보이지 않는 곳으로의 쓰기"를 막는 것이 WITH CHECK OPTION 이다 —
뷰를 통한 INSERT·UPDATE 결과가 뷰의 WHERE 조건을 만족해야만 허용한다.
CREATE VIEW v_dev2 AS
SELECT eno, ename, sal, dno FROM 사원 WHERE dno = 10
WITH CHECK OPTION;
INSERT INTO v_dev2 VALUES (21, '체크', 100, 20); -- dno 20 → 거부
-- ERROR: new row violates check option for view "v_dev2"
INSERT INTO v_dev2 VALUES (21, '체크', 100, 10); -- dno 10 → 성공
UPDATE v_dev2 SET dno = 20 WHERE eno = 21; -- 뷰 밖으로 내보내기 → 거부
-- ERROR: new row violates check option for view "v_dev2"
| 뷰를 통한 작업 | CHECK OPTION 없음 | WITH CHECK OPTION |
|---|---|---|
| 조건 밖 값 INSERT | 성공 (뷰에선 안 보임) | 거부 |
| 조건 밖으로 UPDATE | 성공 (행이 뷰에서 사라짐) | 거부 |
| 조건 안 값 INSERT·UPDATE | 성공 | 성공 |
| 기반 테이블에 직접 INSERT | 성공 | 성공 (CHECK OPTION 은 뷰를 거칠 때만 검사) |
8.5 뷰 위의 뷰와 의존성
CREATE VIEW v_big AS SELECT * FROM v_dept_sum WHERE total >= 1100;
SELECT * FROM v_big;
| dno | cnt | total |
|---|---|---|
| 10 | 3 | 1130 |
(원본 데이터 기준: v_dept_sum 은 10 → 3명·1130, 20 → 3명·1050, NULL → 1명·250.) 김하늘의 급여를 100 내리면 v_dept_sum 의 10 번 합계가 1030 이 되고 v_big 은 0행이 된다 — 두 겹으로 펼쳐져도 여전히 실시간이다.
DROP VIEW v_dept_sum;
-- ERROR: cannot drop view v_dept_sum because other objects depend on it
-- DETAIL: view v_big depends on view v_dept_sum
DROP VIEW v_dept_sum CASCADE; -- v_big 까지 함께 삭제
§7.2 의 테이블 DROP 과 같은 규칙이다. 기반 테이블을 CASCADE 로 지우면 그 위의 뷰들이 줄줄이 지워진다.
8.6 장단점
| 장점 | 설명 |
|---|---|
| 논리적 독립성 | 테이블 구조가 바뀌어도 뷰 정의만 고치면 뷰를 쓰는 프로그램은 그대로 |
| 보안 | 급여 열을 뺀 뷰만 권한을 주면(GRANT SELECT ON v_dev …) 민감 열을 숨길 수 있다 |
| 편의성 | 복잡한 조인·조건을 한 번 정의해 두고 테이블처럼 재사용 |
| 여러 사용자의 다른 관점 | 같은 데이터를 부서별·용도별로 다르게 보여 줌 |
| 단점 | 설명 |
|---|---|
| 독자적 인덱스 불가 | 뷰는 저장된 데이터가 없으니 CREATE INDEX … ON v_dev 불가 ("v_dev" is not a table or materialized view) |
| 정의 변경 불가 | ALTER VIEW 로 정의를 고칠 수 없다 → DROP 후 재생성 / OR REPLACE |
| 갱신 제약 | §8.3 의 조건을 벗어나면 INSERT·UPDATE·DELETE 불가 |
| 성능 착시 | 뷰 조회도 매번 원본 SELECT 를 실행한다(빨라지지 않는다) |
9. INDEX — 정렬된 찾아보기 표
9.1 문법
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블 (열 [ASC|DESC] [, 열 …]);
DROP INDEX 인덱스명; -- 표준·PG·Oracle
DROP INDEX 인덱스명 ON 테이블; -- MySQL·SQL Server
UNIQUE INDEX 는 중복 값을 막는다 — PK·UNIQUE 제약을 걸면 DBMS 가 내부적으로 이것을 자동 생성한다(§3 표). 인덱스는 테이블과
별개의 객체라 테이블에는 영향이 없고, 지워도 데이터는 그대로다. 대신 INSERT·UPDATE·DELETE 때마다 인덱스도 고쳐야 해서 쓰기가 느려진다.
9.2 B-트리 개념
책 뒤의 "찾아보기"처럼 키 값을 정렬해 두고, 각 키 옆에 그 행의 위치(PG: TID, Oracle: ROWID) 를 적어 둔 구조다.
| 단계 | 담긴 것 (name 인덱스 예) |
|---|---|
| 루트 노드 | [ … | 'user5' | … ] → 범위별 자식 포인터 |
| 브랜치 노드 | [ 'user3' | 'user4' | … ] → 다음 단계 포인터 |
| 리프 노드 | ('user777', 행 위치) ('user7770', 행 위치) … 정렬된 키 + 행 위치, 옆 리프와 연결 |
- 모든 리프의 깊이가 같은 균형 트리라 20만 행이어도 3~4 단계면 찾는다 — O(log n). 인덱스가 없으면 전체를 읽는 Full/Seq Scan, O(n).
- 리프가 정렬·연결돼 있어
=뿐 아니라 범위(BETWEEN,<), 접두 일치(LIKE 'abc%'), ORDER BY 에도 쓰인다.
9.3 인덱스가 안 쓰이는 경우 — EXPLAIN 으로 확인
20만 행 테이블을 만들고 실행 계획을 보자(행이 적으면 옵티마이저가 인덱스 대신 Seq Scan 을 고르므로 충분히 넣는다).
CREATE TABLE big (id INTEGER PRIMARY KEY, name VARCHAR(20), code VARCHAR(10), dno INTEGER);
INSERT INTO big SELECT g, 'user' || g, (g % 1000)::text, g % 50
FROM generate_series(1, 200000) g;
CREATE INDEX ix_big_name ON big (name);
CREATE INDEX ix_big_code ON big (code);
ANALYZE big;
EXPLAIN (COSTS OFF) SELECT * FROM big WHERE name = 'user777';
| WHERE 조건 | 실행 계획 (PostgreSQL 14) | 이유 |
|---|---|---|
name = 'user777' | Index Scan using ix_big_name | 정렬된 키로 바로 찾음 |
UPPER(name) = 'USER777' | Seq Scan · Filter: upper(name) | 열을 가공하면 인덱스의 정렬 값과 다른 값이 됨 |
name LIKE '%777' | Seq Scan | 앞이 % 면 어디서 시작할지 모름 (전화번호부에서 "끝이 777" 찾기) |
name <> 'user777' | Seq Scan | 부정 조건 — 거의 전부가 해당돼 인덱스를 타는 이득이 없음 |
CAST(code AS INTEGER) = 7 | Seq Scan · Filter: (code)::integer = 7 | 형 변환이 열 쪽에 걸림 (가공과 같음) |
code = '7' | Bitmap Index Scan on ix_big_code | 타입이 맞으면 사용 |
해결책도 원리와 같다 — 열 쪽을 건드리지 말고 값 쪽을 바꾼다(sal * 12 > 6000 → sal > 500). 정말 가공이 필요하면
함수 기반 인덱스를 만든다. CREATE INDEX ix_big_upper ON big (UPPER(name)); 후에는 UPPER(name) = 'USER777' 도 Bitmap Index Scan 이 된다.
엔진 차이 — MySQL·Oracle 은
code = 7(문자 열 vs 숫자)을 열 쪽을 숫자로 암묵 변환해 실행하므로 인덱스를 못 탄다(시험의 "형 변환" 사례). PostgreSQL 은 아예operator does not exist: character varying = integer오류를 낸다. 또 PG 는 기본 정렬 규칙(로캘)이 C 가 아니면LIKE 'user777%'에도 보통 인덱스를 못 쓰고,CREATE INDEX … (name varchar_pattern_ops)로 만들어야 Index Scan 이 된다(확인함). 부정 조건도 "무조건 못 쓴다"가 아니라 "대부분의 경우 이득이 없어 안 쓴다"가 정확하다.
9.4 복합 인덱스와 선두 열
CREATE INDEX ix_big_dno_name ON big (dno, name); -- (dno 로 먼저 정렬, 같은 dno 안에서 name 정렬)
WHERE 조건 (ix_big_name 을 지운 뒤) | 실행 계획 |
|---|---|
dno = 7 AND name = 'user7' | Index Scan using ix_big_dno_name |
name = 'user7' (선두 열 없음) | Seq Scan |
전화번호부가 (성, 이름) 순으로 정렬돼 있으면 "이름이 민준인 사람"은 처음부터 끝까지 봐야 한다. 복합 인덱스는 선두 열 조건이 있어야
제대로 쓰인다 — 그래서 열 순서는 "항상 조건에 들어가는 열, 그리고 = 로 비교되는 열"을 앞에 둔다.
9.5 클러스터드 vs 넌클러스터드
| 클러스터드(Clustered) 인덱스 | 넌클러스터드(Non-clustered) 인덱스 | |
|---|---|---|
| 데이터 저장 | 테이블 행 자체가 인덱스 키 순서대로 물리 정렬 | 행은 따로, 인덱스는 키 + 행 위치만 |
| 테이블당 | 하나 (물리 순서는 하나뿐) | 여러 개 |
| 비유 | 사전 — 본문이 가나다순 | 책 뒤 찾아보기 — 쪽 번호로 다시 찾아감 |
| 범위 검색 | 매우 빠름(연속 블록) | 키마다 행을 찾아가야 함 |
| 대표 | MySQL InnoDB 의 PK, SQL Server 의 PK(기본) | 그 외 보조 인덱스 |
PostgreSQL 은 테이블이 힙(입력 순)이고 모든 인덱스가 넌클러스터드다. CLUSTER 명령으로 한 번 정렬할 수는 있지만 이후 입력은 다시 흐트러진다.
10. 시퀀스 / AUTO_INCREMENT / IDENTITY / SERIAL
번호를 자동으로 매기는 장치다. 표준은 시퀀스 객체와 IDENTITY 열 두 가지.
CREATE SEQUENCE seq_eno START WITH 100 INCREMENT BY 10;
SELECT nextval('seq_eno') AS a, nextval('seq_eno') AS b, currval('seq_eno') AS c; -- PostgreSQL
| a | b | c |
|---|---|---|
| 100 | 110 | 110 |
nextval 은 다음 값을 꺼내며 증가시키고, currval 은 이 세션에서 마지막으로 꺼낸 값을 다시 보여 준다. 꺼낸 번호는 롤백해도
돌아오지 않는다 — 시퀀스 번호에 빈 번호(gap)가 생기는 것은 정상이다.
| DBMS | 문법 | 다음 값 |
|---|---|---|
| 표준 / PG 10+ / Oracle 12c+ | id INTEGER GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY | 자동 |
| PostgreSQL (옛 방식) | id SERIAL (= INTEGER + 시퀀스 + DEFAULT nextval) | 자동 |
| Oracle | CREATE SEQUENCE s …; → INSERT … VALUES (s.NEXTVAL, …) | s.NEXTVAL, s.CURRVAL |
| PostgreSQL | 위와 같음 | nextval('s'), currval('s') |
| MySQL | id INT AUTO_INCREMENT PRIMARY KEY | 자동, LAST_INSERT_ID() |
| SQLite | id INTEGER PRIMARY KEY [AUTOINCREMENT] | 자동 (rowid) |
| SQL Server | id INT IDENTITY(1,1) | 자동 |
GENERATED ALWAYS 열에 값을 직접 넣으면 거부된다 — cannot insert a non-DEFAULT value into column "id"(PG). BY DEFAULT 는 직접 넣는 값을 허용한다.
11. 시스템 카탈로그(데이터 사전)
테이블·열·제약·뷰·인덱스·사용자·권한 같은 메타데이터(데이터에 관한 데이터) 를 DBMS 가 스스로 저장하는 테이블 모음이다.
- DDL 을 실행하면 DBMS 가 카탈로그를 자동으로 갱신한다. 사용자는 SELECT 로 조회만 하고 직접 INSERT·UPDATE 하지 않는다.
- 카탈로그도 테이블이므로 SQL 로 조회한다. 표준은
INFORMATION_SCHEMA스키마의 뷰들.
SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = '부서'
ORDER BY ordinal_position;
| table_name | column_name | data_type | is_nullable |
|---|---|---|---|
| 부서 | dno | integer | NO |
| 부서 | dname | character varying | NO |
| 부서 | loc | character varying | YES |
information_schema.table_constraints 에서는 §2.2 에서 붙인 이름 pk_사원(PRIMARY KEY), fk_사원_부서·fk_사원_mgr(FOREIGN KEY), ck_bonus(CHECK) 가 그대로 보인다.
| DBMS | 카탈로그 조회 |
|---|---|
| 표준 / PG / MySQL | INFORMATION_SCHEMA.TABLES, .COLUMNS, .TABLE_CONSTRAINTS, .VIEWS |
| PostgreSQL 고유 | pg_catalog.pg_class, pg_attribute, pg_indexes / psql \d 테이블 |
| Oracle | USER_TABLES, USER_TAB_COLUMNS, USER_CONSTRAINTS, USER_VIEWS (ALL_·DBA_ 접두는 범위만 넓힘) |
| MySQL | SHOW TABLES, DESC 테이블, SHOW INDEX FROM 테이블 |
| SQLite | sqlite_master(정의 SQL 저장), PRAGMA table_info('t') |
확인 문제
CREATE TABLE u (k INTEGER UNIQUE);에INSERT INTO u VALUES (NULL);(NULL)(1)(1)을 한 문장씩 실행했다. 남는 행 수는?- (O/X)
sal INTEGER CHECK (sal > 0)열에 NULL 을 INSERT 하면 CHECK 위반 오류가 난다. - §4.1 의 d2·e2 에서
ON DELETE SET NULL로 FK 를 건 뒤DELETE FROM d2 WHERE dno = 10;을 실행했다. e2 의 (eno, dno) 를 모두 쓰라. - MySQL 에서 사원.ename 을 VARCHAR(40) 으로 바꾸는 문장의 빈칸:
ALTER TABLE 사원 ( ① ) ename VARCHAR(40); - 다음 설명에 해당하는 명령은? — "테이블 구조는 남기고 모든 행을 지운다. WHERE 를 쓸 수 없고, Oracle 에서는 롤백할 수 없다."
- §8.4 의
v_dev2(WHERE dno = 10 WITH CHECK OPTION)에INSERT INTO v_dev2 VALUES (30, '테스트', 100, 20);을 실행하면? name열에 B-트리 인덱스가 있다. 인덱스를 쓰기 어려운 조건을 모두 고르라. ㉠name = '김하늘'㉡SUBSTR(name, 1, 1) = '김'㉢name LIKE '%늘'㉣name BETWEEN '가' AND '나'- (O/X)
CREATE TABLE 사원2 AS SELECT * FROM 사원;으로 만든 사원2 에는 사원의 PRIMARY KEY 가 그대로 복사된다.
정답과 풀이
- 3행 — NULL 끼리는 같다고 판정되지 않으므로(
NULL = NULL→ UNKNOWN) 두 NULL 모두 들어간다. 두 번째1만 중복 오류. (SQL Server 라면 2행) - X —
NULL > 0은 UNKNOWN 이고, CHECK 는 FALSE 일 때만 거부한다. NULL 을 막으려면 NOT NULL 을 함께 건다. - (1, NULL), (2, NULL), (4, 20) — 부서 10 을 참조하던 두 행의 FK 만 NULL 이 되고 행은 남는다. CASCADE 였다면 (4, 20) 한 행만 남는다.
- ① MODIFY — 표준·PG 는
ALTER COLUMN ename TYPE VARCHAR(40), Oracle 은MODIFY (ename VARCHAR2(40)). - TRUNCATE — DELETE 는 WHERE 가능·롤백 가능, DROP 은 구조까지 삭제. (PostgreSQL 은 트랜잭션 안의 TRUNCATE 도 롤백된다.)
- 오류(
new row violates check option) — 넣는 행의 dno 20 이 뷰 조건dno = 10을 만족하지 않는다. CHECK OPTION 이 없었다면 들어가되 뷰에서는 안 보인다. - ㉡, ㉢ — ㉡ 열을 함수로 가공, ㉢ 앞이
%. ㉣ 범위 조건은 정렬된 리프를 따라가므로 인덱스에 잘 맞는다. - X — CTAS 는 열 이름·타입과 데이터만 복사한다. PK·UNIQUE·CHECK·FK·DEFAULT·인덱스는 빠진다(Oracle 은 NOT NULL 만 복사). 필요하면
ALTER TABLE 사원2 ADD PRIMARY KEY (eno);.
한 줄 요약
| 주제 | 기억할 한 줄 |
|---|---|
| CHAR vs VARCHAR | CHAR 는 고정 길이로 공백 패딩, 비교 시 뒤 공백 무시. VARCHAR 는 가변(Oracle VARCHAR2) |
| NUMERIC(p, s) | p = 전체 자릿수, s = 소수 자릿수. 소수부 초과는 반올림, 정수부 초과는 오류 |
| 열/테이블 수준 | 복합 키·열끼리 비교 CHECK 는 테이블 수준, NOT NULL 은 열 수준만 |
| UNIQUE / PK | UNIQUE 는 NULL 여러 개 허용(SQL Server 제외), PK = UNIQUE + NOT NULL, 테이블당 하나 |
| CHECK | FALSE 일 때만 거부 — UNKNOWN(NULL) 은 통과 |
| DEFAULT | 열을 생략했을 때만 적용, NULL 을 명시하면 NULL |
| FK 참조 동작 | CASCADE 같이 삭제 · SET NULL · SET DEFAULT · RESTRICT 즉시 거부 · NO ACTION 문장 끝(지연 가능) 거부 |
| 무결성 | 개체 = PK 는 NULL·중복 불가 · 참조 = FK 는 부모 값 또는 NULL · 도메인 = 타입·CHECK |
| ALTER | 표준·PG ALTER COLUMN … TYPE, Oracle·MySQL MODIFY. 기존 데이터가 어기면 제약 추가 실패 |
| DELETE / TRUNCATE / DROP | DML·WHERE·트리거 / DDL·전체 행·구조 유지 / DDL·구조까지. Oracle 은 DDL 롤백 불가, PG 는 가능 |
| DROP CASCADE | 의존 객체(뷰 등)까지 삭제. RESTRICT(기본)는 의존 객체 있으면 거부 |
| CTAS | 데이터·타입만 복사, 제약·인덱스는 복사 안 됨. WHERE 1 = 0 이면 빈 구조 |
| VIEW | 저장된 SELECT, 항상 최신. 독립성·보안·편의 / 인덱스 불가·ALTER 불가·갱신 제약 |
| WITH CHECK OPTION | 뷰를 통한 INSERT·UPDATE 결과가 뷰 조건을 벗어나면 거부 |
| INDEX | B-트리 = 정렬 키 + 행 위치. 열 가공·앞 %·부정·형 변환이면 못 씀, 복합 인덱스는 선두 열부터 |
| 클러스터드 | 행 자체가 키 순서로 저장, 테이블당 하나 (InnoDB PK) |
| 자동 번호 | 표준 IDENTITY / PG SERIAL·nextval / Oracle 시퀀스.NEXTVAL / MySQL AUTO_INCREMENT |
| 데이터 사전 | DBMS 가 자동 관리하는 메타데이터, 사용자는 조회만 (INFORMATION_SCHEMA, Oracle USER_*) |