SQL INSERT·UPDATE·DELETE 와 제약 조건, 직접 깨뜨려 보기

시리즈: SQL 강좌 2/6 (초급). SELECT 로 데이터를 읽을 줄 알게 됐고(1강), 이제 다른 사람들도 쓰는 데이터베이스의 데이터를 직접 바꿔야 하는 분을 위한 글입니다. 다 읽으면 행을 넣고·고치고·지우고, 잘못된 데이터는 제약 조건이 대신 막게 하고, "WHERE 없는 UPDATE" 사고를 아무 일도 아닌 것으로 만드는 습관 하나를 갖게 됩니다.

읽기는 안전하지만 쓰기는 다릅니다. 어느 팀이든 WHERE 를 빼먹은 UPDATE 로 운영 DB 의 회원 등급이 전부 바뀌었다는 이야기, 이미 탈퇴한 회원을 가리키는 주문이 수백 건 남아 있었다는 이야기가 하나씩은 있습니다. 이 글은 그런 사고를 일부러, 버려도 되는 데이터베이스에서 재현하고 SQLite 가 내는 메시지를 그대로 보여 줍니다. 모든 출력은 Apple Silicon Mac(M1 Pro)의 sqlite3 3.51.0 셸에서 실제로 실행한 결과입니다.

준비: 규칙이 들어간 반 명단

반(classes)과 그 반의 학생(students)으로 된 작은 학교 데이터베이스를 씁니다. 테이블 정의에 제약 조건, 즉 데이터베이스가 변경 때마다 검사하는 규칙이 들어 있습니다. school.sql 로 저장하고 sqlite3 school.db < school.sql 로 실행합니다.

PRAGMA foreign_keys = ON;
CREATE TABLE classes (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
CREATE TABLE students (
  id       INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  name     TEXT NOT NULL,
  score    INTEGER CONSTRAINT score_range CHECK (score BETWEEN 0 AND 100),
  class_id INTEGER REFERENCES classes(id)
);
INSERT INTO classes (name) VALUES ('1-A'), ('1-B');
INSERT INTO students (username, name, score, class_id) VALUES
  ('mina01', 'Mina', 82, 1),
  ('jun02',  'Jun',  67, 1),
  ('sora03', 'Sora', 91, 2),
  ('leo04',  'Leo',  55, 2);

각 규칙의 뜻:

• PRIMARY KEY(기본 키): 행을 식별합니다. SQLite 의 INTEGER PRIMARY KEY 는 특별해서, 값을 빼면 다음 번호를 자동으로 붙입니다.
• NOT NULL: 값이 반드시 있어야 합니다.
• UNIQUE: 같은 값을 가진 행이 둘 있으면 안 됩니다(아이디 중복 금지).
• CHECK: 직접 쓰는 아무 조건. CONSTRAINT score_range 처럼 이름을 붙이면 오류 메시지가 읽기 쉬워집니다.
• REFERENCES(외래 키): class_id 는 classes 에 실제로 있는 행을 가리켜야 합니다.

그다음 sqlite3 school.db 로 대화형으로 열고 .headers on, .mode table 을 입력합니다.

INSERT, 그리고 제약 조건이 거부할 때 하는 말

좋은 INSERT 는 열 이름을 적습니다. 나중에 누가 열을 추가해도 계속 동작하기 때문입니다.

INSERT INTO students (username, name, score, class_id) VALUES ('hana05', 'Hana', 74, 1);
SELECT last_insert_rowid();     -- 5: 방금 SQLite 가 붙인 id

이제 제약 조건마다 하나씩, 잘못된 INSERT 네 개입니다.

INSERT INTO students (username, score, class_id) VALUES ('noname', 50, 1);
INSERT INTO students (username, name, score, class_id) VALUES ('mina01', 'Mina Two', 70, 1);
INSERT INTO students (username, name, score, class_id) VALUES ('kai06', 'Kai', 105, 1);
INSERT INTO students (username, name, score, class_id) VALUES ('kai06', 'Kai', NULL, 1);

Runtime error near line 6: NOT NULL constraint failed: students.name (19)
Runtime error near line 7: UNIQUE constraint failed: students.username (19)
Runtime error near line 8: CHECK constraint failed: score_range (19)

(메시지의 줄 번호는 우리 스크립트 파일 기준입니다.) 세 번 거부됐고, 네 번째는 성공했습니다. 카이는 점수가 빈 채로 들어가 있습니다. CHECK 는 조건이 거짓일 때만 실패하는데, NULL BETWEEN 0 AND 100 은 "모름"이라 통과합니다. 1강의 NULL 논리 그대로입니다. 점수가 필수라면 NOT NULL 도 함께 붙여야 합니다. (19) 는 "제약 조건 위반"을 뜻하는 SQLite 코드이고, 앱에서는 예외(파이썬이면 sqlite3.IntegrityError)로 받게 됩니다.

FOREIGN KEY: 켜기 전까지는 꺼져 있다

새 sqlite3 school.db 세션에서, 존재하지 않는 99반에 학생을 넣어 봤습니다.

INSERT INTO students (username, name, score, class_id) VALUES ('ghost', 'Ghost', 60, 99);
PRAGMA foreign_keys;

+--------------+
| foreign_keys |
+--------------+
| 0            |
+--------------+

오류가 없습니다. 설정 스크립트 첫 줄에 PRAGMA foreign_keys = ON 이 있었는데도 들어갔습니다. SQLite 의 외래 키 검사는 기본값이 꺼짐이고 연결마다 따로 켜야 하며, 이 설정은 파일에 저장되지 않습니다. 셸 세션이든 파이썬 스크립트든 휴대폰 앱이든, 새 연결마다 다시 켜야 합니다. 꺼져 있는 동안 몰래 들어온 행은 이렇게 찾습니다.

PRAGMA foreign_key_check;

+----------+-------+---------+------+
|  table   | rowid | parent  | fkid |
+----------+-------+---------+------+
| students | 7     | classes | 0    |
+----------+-------+---------+------+

검사를 켜면 같은 실수가 양쪽 방향 모두에서 막힙니다.

PRAGMA foreign_keys = ON;
INSERT INTO students (username, name, score, class_id) VALUES ('ghost2', 'Ghost2', 60, 99);
DELETE FROM classes WHERE id = 2;                 -- 1-B 반에 아직 학생이 있음
UPDATE students SET class_id = 3 WHERE username = 'leo04';

Runtime error near line 6: FOREIGN KEY constraint failed (19)
Runtime error near line 7: FOREIGN KEY constraint failed (19)
Runtime error near line 10: FOREIGN KEY constraint failed (19)

이 메시지는 다른 것들과 달리 어느 열·어느 테이블이 문제인지 알려 주지 않습니다. 외래 키가 여러 개인 테이블이면 직접 추적해야 하고, PRAGMA foreign_key_list(students) 로 목록을 볼 수 있습니다. 유령 학생은 DELETE FROM students WHERE username = 'ghost'; 로 지웠습니다(changes() 는 1).

WHERE 없는 UPDATE 사고와 트랜잭션이라는 안전망

UPDATE 는 WHERE 에 맞는 모든 행의 열을 바꾸고, DELETE 는 맞는 모든 행을 지웁니다. WHERE 를 빼면 "맞는 모든 행"이 "모든 행"이 됩니다. 학생 한 명의 점수만 초기화하려다 한 줄 일찍 엔터를 친 상황입니다.

BEGIN;
UPDATE students SET score = 0;
SELECT id, name, score FROM students;

changes: 6   total_changes: 6
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1  | Mina | 0     |
| 2  | Jun  | 0     |
| 3  | Sora | 0     |
| 4  | Leo  | 0     |
| 5  | Hana | 0     |
| 6  | Kai  | 0     |
+----+------+-------+

BEGIN 을 먼저 쳤기 때문에 아직 아무것도 확정되지 않았습니다. 트랜잭션은 여러 문장을 묶어서 COMMIT 하면 한꺼번에 반영하고, ROLLBACK 하면 전혀 반영하지 않습니다.

ROLLBACK;
SELECT id, name, score FROM students;

+----+------+-------+
| id | name | score |
+----+------+-------+
| 1  | Mina | 82    |
| 2  | Jun  | 67    |
| 3  | Sora | 91    |
| 4  | Leo  | 55    |
| 5  | Hana | 74    |
| 6  | Kai  |       |
+----+------+-------+

점수가 모두 돌아왔습니다. BEGIN 이 없으면 문장 하나하나가 그 자리에서 커밋되고(자동 커밋), 되돌릴 방법은 백업뿐입니다. 중요한 DB 를 손으로 고칠 때의 습관:

1. 같은 WHERE 로 먼저 SELECT 해서 건수를 셉니다.
2. BEGIN;
3. UPDATE 나 DELETE 를 실행하고 changes(또는 SELECT changes();)를 1번의 건수와 비교합니다.
4. 맞으면 COMMIT;, 다르면 ROLLBACK;.

함정이 하나 더 있습니다. 새로 복사한 DB 에서 시험해 보니, 트랜잭션 안에서 오류가 나도 트랜잭션 전체가 롤백되지 않고 실패한 문장만 취소됩니다.

BEGIN;
INSERT INTO students (username, name, score, class_id) VALUES ('rin07', 'Rin', 88, 2);
INSERT INTO students (username, name, score, class_id) VALUES ('mina01', 'Mina again', 70, 1);
COMMIT;

Runtime error near line 5: UNIQUE constraint failed: students.username (19)

실행 후 rin07 은 테이블에 들어가 있었습니다. 성공한 절반을 COMMIT 이 저장해 버린 것입니다. 같은 스크립트 첫 줄에 .bail on 을 넣고 돌리자 셸이 오류에서 종료 코드 1 로 멈췄고, COMMIT 은 실행되지 않아 rin07 은 저장되지 않았습니다(건수 0). 애플리케이션 코드라면 "예외를 잡아서 rollback 호출"이 같은 역할입니다.

UPDATE·DELETE 제대로 하기: changes 와 RETURNING

이제 위의 습관대로 의도한 변경을 세 번 합니다. 셸에서 .changes on 을 켜면 문장마다 바뀐 행 수가 찍힙니다(SELECT changes(); 는 어디서나 같은 값을 줍니다).

먼저 BEGIN … COMMIT 안에서 UPDATE students SET score = score + 5 WHERE class_id = 1 을 실행하니 changes: 4 였지만, 카이의 점수는 여전히 비어 있었습니다. NULL + 5 는 NULL 이니까요. "4행이 바뀜"이 "4행에 숫자가 들어감"을 뜻하지는 않습니다.

이어서 두 개를 더, RETURNING(SQLite 3.35+)을 붙여 실행합니다. 문장이 건드린 행을 그대로 보여 줍니다.

UPDATE students SET score = 100 WHERE name = 'Sora' RETURNING id, name, score;
DELETE FROM students WHERE id = 6 RETURNING *;

+----+------+-------+
| id | name | score |
+----+------+-------+
| 3  | Sora | 100   |
+----+------+-------+
changes: 1   total_changes: 11
+----+----------+------+-------+----------+
| id | username | name | score | class_id |
+----+----------+------+-------+----------+
| 6  | kai06    | Kai  |       | 1        |
+----+----------+------+-------+----------+
changes: 1   total_changes: 12

키에 관한 흔한 실수: 무슨 일이 일어나고 어떻게 고치나

INSERT OR REPLACE 는 UPDATE 가 아닙니다. 미나의 이름만 바꾸려고 했습니다.

INSERT OR REPLACE INTO students (username, name) VALUES ('mina01', 'Mina K.');
SELECT id, username, name, score, class_id FROM students WHERE username LIKE 'mina%';

+----+----------+---------+-------+----------+
| id | username |  name   | score | class_id |
+----+----------+---------+-------+----------+
| 6  | mina01   | Mina K. |       |          |
+----+----------+---------+-------+----------+

원래 미나는 id 1, 점수 87, 1반이었습니다. REPLACE 는 그 행을 삭제하고 새 행을 넣기 때문에 id 가 바뀌고, 지정하지 않은 열은 NULL 이 됐습니다. "학생 1번"을 저장해 둔 다른 데이터는 이제 아무것도 가리키지 않습니다. MySQL 의 REPLACE INTO 도 같은 방식이라 실무에서 자주 사고가 납니다. 해결책은 기존 행을 그 자리에서 고치는 업서트입니다.

INSERT INTO students (username, name, score, class_id) VALUES ('jun02', 'Jun', 90, 1)
  ON CONFLICT (username) DO UPDATE SET score = excluded.score;

준은 id 2 와 1반을 유지했고 점수만 90 이 됐습니다(excluded 는 "넣으려고 했던 행"). 세 번째 선택지 INSERT OR IGNORE 는 충돌하면 조용히 아무것도 하지 않습니다(changes: 0).

id 는 재사용됩니다. 위 미나의 새 id 를 보세요. 6 은 바로 전에 지운 카이의 id 였습니다. 확인하려고 가장 큰 id(6) 행을 한 번 더 지우고 새 학생을 넣었더니 새 행이 또 id 6 을 받았습니다. AUTOINCREMENT 키워드가 없으면 SQLite 는 "가장 큰 id + 1"을 쓰므로, 다른 테이블이나 로그가 예전 6번을 기억하고 있다면 이제 엉뚱한 사람을 가리킵니다. id 가 절대 돌아오면 안 된다면 AUTOINCREMENT 를 붙입니다.

TEXT 기본 키는 NULL 이 될 수 있습니다. 호환성 때문에 남겨 둔 오래된 SQLite 버그로, 정수가 아닌 기본 키는 NULL 을, 그것도 여러 번 받아 줍니다.

CREATE TABLE tags (code TEXT PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (NULL, 'first'), (NULL, 'second');   -- 둘 다 들어감
CREATE TABLE tags2 (code TEXT PRIMARY KEY NOT NULL, label TEXT);
INSERT INTO tags2 VALUES (NULL, 'first');

Runtime error near line 16: NOT NULL constraint failed: tags2.code (19)

정수가 아닌 키에는 NOT NULL 을 명시합니다(1강의 STRICT 테이블은 이걸 자동으로 강제합니다).

판단표: 어떤 규칙에 무엇을 쓰나

실측: DELETE 를 3,600배 느리게 만든 외래 키

제약 조건은 공짜가 아니고, 그중 하나는 거의 모두를 놀라게 합니다. 외래 키를 켠 상태에서 부모 행을 지우면, SQLite 는 그 행을 가리키는 자식 행이 남아 있지 않은지 확인해야 합니다. 자식 열(students.class_id)에 인덱스가 없으면 이 확인이 자식 테이블 전체를 읽고, 그것을 지우는 부모 행마다 반복합니다.

반 2,000개와 학생 200,000명을 만들고(학생의 반 번호는 고정 공식으로 1~1,000반에 고르게 분포, 난수 없음), 학생이 없는 1,001~1,100번 반 100개를 지웠습니다. 반복할 수 있게 각 삭제는 BEGIN … ROLLBACK 안에서 실행했고, 파이썬 sqlite3 모듈(SQLite 3.51.0)로 5회 중앙값을 M1 Pro 에서 쟀습니다.

con.execute("PRAGMA foreign_keys = ON")
con.execute("BEGIN")
t = time.perf_counter()
con.execute("DELETE FROM classes WHERE id BETWEEN 1001 AND 1100")   # 100행
elapsed = time.perf_counter() - t
con.execute("ROLLBACK")

외래 키를 켜는 것은 옳은 선택이지만, 자식 열 인덱스도 같이 만들어야 합니다. CREATE INDEX 한 줄로 삭제가 검사하지 않을 때와 같은 속도, 약 3,600배 빠르게 돌아왔습니다. SQLite 는 이 인덱스를 자동으로 만들어 주지 않습니다(MySQL 의 InnoDB 는 만들어 줌, 여기서는 실행하지 않음). 인덱스는 4강에서 제대로 다룹니다.

한계와 버전 메모

• SQLite 3.51.0 에서만 실행했습니다(셸과 파이썬 3.9 sqlite3 모듈 모두 3.51.0). SQLite 릴리스 노트 기준으로 업서트(ON CONFLICT … DO UPDATE)는 3.24.0, RETURNING 은 3.35.0, 외래 키 지원은 3.6.19 부터입니다. 오래된 안드로이드에는 더 낮은 버전의 SQLite 가 들어 있습니다.
• INSERT OR REPLACE, OR IGNORE, NULL 이 되는 TEXT 기본 키는 SQLite 의 동작입니다. MySQL 에는 REPLACE INTO(역시 삭제 후 삽입)와 INSERT … ON DUPLICATE KEY UPDATE 가, PostgreSQL 에는 SQLite 와 비슷한 ON CONFLICT 가 있습니다(여기서는 실행하지 않음).
• PostgreSQL 은 SQLite 와 달리 트랜잭션 안에서 오류가 나면 롤백할 때까지 트랜잭션 전체가 중단 상태가 됩니다(여기서는 실행하지 않음).
• 측정은 노트북 한 대, OS 캐시에 올라온 데이터 기준입니다. 3,600배라는 비율은 자식 테이블 크기(여기서는 20만 행)에 따라 달라지고, 클수록 커집니다.

연습 문제

사고 장면 직전의 명단(학생 6명, 외래 키 켬)에서 시작합니다.

1. 학생 yuna08(Yuna, 77점)을 1-B 반에 넣고, 1-B 반 전원에게 3점씩 더 주되 100점을 넘지 않게 하세요.
2. 1-B 반을 1-A 반에 합치기로 했습니다. 외래 키가 켜져 있으면 DELETE FROM classes WHERE name = '1-B'; 는 실패합니다. 안전하게 합쳐 보세요.

정답

1.

INSERT INTO students (username, name, score, class_id) VALUES ('yuna08', 'Yuna', 77, 2);
UPDATE students SET score = min(score + 3, 100) WHERE class_id = 2;

changes: 3 이고, 반 점수는 Sora 94, Leo 58, Yuna 80 이 됐습니다. 인자가 두 개인 min() 은 작은 쪽을 돌려주므로 99점은 102 가 아니라 100 이 됩니다.

2. 학생을 먼저 옮기고 반을 지우는 것을 한 트랜잭션으로 묶습니다.

BEGIN;
UPDATE students SET class_id = 1 WHERE class_id = 2;   -- changes: 3
DELETE FROM classes WHERE name = '1-B';                -- changes: 1
COMMIT;

바로 지우려던 시도는 FOREIGN KEY constraint failed (19) 였습니다. 트랜잭션 후 classes 에는 1-A 만 남았고, 학생 7명 모두 1반이 됐습니다.

다음 강좌

3강은 여러 테이블을 가로질러 읽습니다. JOIN, GROUP BY 와 HAVING, COUNT(*) 와 COUNT(열) 이 다른 이유, 합계를 두 배로 부풀리는 "행 폭발", 그리고 서브쿼리와 JOIN 중 무엇이 빠른지(실측)를 다룹니다.

• 이전: SQL 강좌 1/6: SELECT·WHERE·ORDER BY 와 NULL
• 다음: SQL 강좌 3/6: JOIN·GROUP BY·서브쿼리
• 이후: 인덱스와 EXPLAIN QUERY PLAN, 트랜잭션·격리 수준·잠금, 윈도 함수와 CTE
• 전체 학습 순서: 신입 개발자를 위한 프로그래밍 기초 학습 로드맵
• 코드에서 DB 에 안전하게 쓰기: PHP PDO와 SQLite: SQL 인젝션을 직접 재현하고 고치기