SQL INSERT, UPDATE, DELETE y restricciones, probados

Serie: curso de SQL 2/6 (principiante). Ya sabes leer datos con SELECT (clase 1) y ahora te toca modificarlos: quizá en tu primera semana en una startup te pidieron "corregir unos registros en producción", o como freelancer administras la base de datos de un cliente que no tiene a nadie más. Al terminar esta clase vas a insertar, actualizar y borrar filas, dejar que las restricciones rechacen los datos malos por ti y tener el único hábito que convierte el accidente del "UPDATE sin WHERE" en una anécdota sin consecuencias.

Leer datos es seguro; cambiarlos es donde la carrera se pone interesante. Todo equipo tiene una historia sobre el UPDATE que olvidó su WHERE, o sobre filas huérfanas que apuntaban a un cliente que ya no existía. En esta clase reproducimos esos incidentes a propósito, en una base de datos desechable, y mostramos los mensajes exactos que imprime SQLite. Todo lo que sigue se ejecutó con la shell sqlite3 3.51.0 en una Mac con Apple Silicon (M1 Pro), y cada salida está copiada de la ejecución real.

Preparación: una lista de alumnos con reglas incluidas

Vamos a llevar una pequeña base de datos escolar: clases (grupos) y alumnos en esas clases. Las definiciones de las tablas llevan restricciones (constraints), reglas que la base de datos revisa en cada cambio. Guarda esto como school.sql y ejecuta 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);

Qué significa cada regla:

• PRIMARY KEY: identifica una fila. INTEGER PRIMARY KEY es especial en SQLite: si no la pasas, SQLite asigna el siguiente número.
• NOT NULL: la columna debe tener un valor.
• UNIQUE: dos filas no pueden compartir el valor (dos alumnos no pueden tener el mismo username).
• CHECK: cualquier condición que escribas. Ponerle nombre (CONSTRAINT score_range) hace legible el mensaje de error.
• REFERENCES (una clave foránea, foreign key): class_id debe apuntar a una fila existente de classes.

Después abre la base de datos en modo interactivo con sqlite3 school.db y escribe .headers on y .mode table.

INSERT, y qué dice cada restricción cuando te rechaza

Un buen insert nombra las columnas, así sigue funcionando cuando alguien agregue una columna más adelante:

INSERT INTO students (username, name, score, class_id) VALUES ('hana05', 'Hana', 74, 1);
SELECT last_insert_rowid();     -- 5: the id SQLite just assigned

Ahora cuatro inserts malos, uno por restricción:

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)

(Los números de línea de estos mensajes se refieren a nuestro archivo de script.) Tres rechazos, y el cuarto insert funcionó: Kai está en la tabla con la nota vacía. Un CHECK solo falla cuando su condición es falsa; NULL BETWEEN 0 AND 100 es desconocido, así que pasa, con la misma lógica de NULL de la clase 1. Si la nota es obligatoria, agrega también NOT NULL. El (19) es el código de SQLite para "violación de restricción", que tu app ve como una excepción (sqlite3.IntegrityError en Python, por ejemplo).

FOREIGN KEY: apagada hasta que la enciendes

Luego insertamos a un alumno en la clase 99, que no existe, en una nueva sesión de sqlite3 school.db:

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

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

Ningún error. El insert pasó, aunque el script de preparación empezaba con PRAGMA foreign_keys = ON. En SQLite, la verificación de claves foráneas está desactivada por defecto y es por conexión, y el pragma no se guarda en el archivo. Cada conexión nueva, sea una sesión de la shell, un script de Python o la app en un celular, tiene que volver a activarla. Para encontrar las filas que se colaron mientras estaba apagada:

PRAGMA foreign_key_check;

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

Con la verificación activada, los mismos errores fallan, en las dos direcciones:

PRAGMA foreign_keys = ON;
INSERT INTO students (username, name, score, class_id) VALUES ('ghost2', 'Ghost2', 60, 99);
DELETE FROM classes WHERE id = 2;                 -- class 1-B still has students
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)

Fíjate en que este mensaje, a diferencia de los otros, no dice qué columna ni qué tabla falló. Si una tabla tiene varias claves foráneas, te toca averiguarlo; PRAGMA foreign_key_list(students) las lista. Después eliminamos al fantasma con DELETE FROM students WHERE username = 'ghost'; (changes() devolvió 1).

El accidente del UPDATE sin WHERE, y cómo te salva una transacción

UPDATE cambia columnas en cada fila que cumple el WHERE; DELETE borra cada fila que lo cumple. Si omites el WHERE, "cada fila que lo cumple" significa todas las filas. Este es el accidente: querías resetear la nota de un alumno y presionaste Enter una línea antes de tiempo:

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     |
+----+------+-------+

Como escribimos BEGIN primero, todavía nada es permanente. Una transacción agrupa sentencias para que tengan efecto juntas con COMMIT, o no tengan ningún efecto con 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  |       |
+----+------+-------+

Todas las notas volvieron. Sin BEGIN, cada sentencia se confirma sola (autocommit), y la única forma de volver atrás es un respaldo. El hábito para cualquier cambio manual en una base de datos que importa:

1. Ejecuta el WHERE como un SELECT primero y cuenta las filas.
2. BEGIN;
3. Ejecuta el UPDATE o DELETE y compara changes (o SELECT changes();) con el paso 1.
4. COMMIT; si los números coinciden, ROLLBACK; si no.

Una trampa más, probada sobre una copia nueva de la base de datos. Un error dentro de una transacción no revierte la transacción; solo cancela la sentencia que falló:

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)

Después, rin07 estaba en la tabla: el COMMIT guardó la mitad que funcionó. Cuando ejecutamos el mismo script con .bail on como primera línea, la shell se detuvo en el error con código de salida 1, el COMMIT nunca corrió y rin07 no se guardó (conteo 0). En el código de una aplicación, el equivalente es: captura la excepción y llama a rollback.

UPDATE y DELETE bien hechos: changes y RETURNING

Ahora tres cambios deliberados, usando el hábito de arriba. En la shell, .changes on imprime cuántas filas tocó cada sentencia (SELECT changes(); hace lo mismo en cualquier lado).

Primero, dentro de BEGIN … COMMIT, UPDATE students SET score = score + 5 WHERE class_id = 1 reportó changes: 4, pero la nota de Kai siguió vacía, porque NULL + 5 es NULL. "4 filas cambiadas" no significa "4 filas recibieron un número".

Luego dos más, con RETURNING (SQLite 3.35+), que muestra las filas que tocó una sentencia:

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

Errores comunes con las claves: qué pasa y cómo arreglarlo

INSERT OR REPLACE no es un update. Queríamos cambiarle el nombre a Mina:

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. |       |          |
+----+----------+---------+-------+----------+

Antes, ella era el id 1 con nota 87 en la clase 1. REPLACE borró esa fila e insertó una nueva, así que su id cambió y las columnas que no mencionamos quedaron en NULL. Todo lo que guardaba "alumno 1" ahora no apunta a nada. La solución es un upsert, que actualiza la fila existente en su lugar:

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

Jun conservó el id 2 y la clase 1, y su nota pasó a 90. (excluded significa "la fila que intentamos insertar".) INSERT OR IGNORE es la tercera opción: ante un conflicto no hace nada, en silencio (changes: 0).

Los ids se reutilizan. Mira el nuevo id de Mina arriba: 6 es el id que tenía Kai hasta que lo borramos un momento antes. Para confirmarlo, borramos otra vez la fila con el id más alto (6) e insertamos un alumno nuevo; la fila nueva volvió a recibir el id 6. Sin la palabra clave AUTOINCREMENT, SQLite usa "id más grande + 1", así que si otra tabla o un log todavía se refiere al antiguo 6, ahora se refiere a otra persona. Agrega AUTOINCREMENT si los ids nunca deben volver a usarse.

Una clave primaria TEXT puede ser NULL. Por un viejo bug de SQLite que se conserva por compatibilidad, una clave primaria no entera acepta NULL, y más de una vez:

CREATE TABLE tags (code TEXT PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (NULL, 'first'), (NULL, 'second');   -- both accepted
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)

Escribe NOT NULL explícitamente en las claves que no son enteras (las tablas STRICT de la clase 1 lo hacen cumplir por ti).

Tabla de decisión: qué herramienta para qué regla

Una medición: la clave foránea que hizo el DELETE 3,600 veces más lento

Las restricciones no son gratis, y hay un costo que sorprende a casi todo el mundo. Cuando borras una fila padre con las claves foráneas activadas, SQLite tiene que comprobar que ninguna fila hija siga apuntándole. Si la columna hija (students.class_id) no tiene índice, esa comprobación lee toda la tabla hija, por cada fila padre que borras.

Construimos 2,000 clases y 200,000 alumnos (ids de clase repartidos de forma pareja entre las clases 1 a 1,000 con una fórmula fija, sin azar) y luego borramos las 100 clases con ids del 1,001 al 1,100, que no tienen alumnos. Cada borrado corrió dentro de BEGIN … ROLLBACK para poder repetirlo: 5 veces, mediana, con el módulo sqlite3 de Python (SQLite 3.51.0) en la 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 rows
elapsed = time.perf_counter() - t
con.execute("ROLLBACK")

Así que activar las claves foráneas es la decisión correcta, pero indexa la columna hija al mismo tiempo. Un solo CREATE INDEX devolvió el borrado a la velocidad de no verificar nada, unas 3,600 veces más rápido. SQLite no crea ese índice por ti (InnoDB de MySQL sí; no ejecutado aquí). La clase 4 trata los índices a fondo.

Límites honestos y notas de versión

• Ejecutado solo en SQLite 3.51.0 (shell y módulo sqlite3 de Python 3.9, ambos 3.51.0). Según las notas de versión de SQLite, el upsert (ON CONFLICT … DO UPDATE) llegó en la 3.24.0 y RETURNING en la 3.35.0; el soporte de claves foráneas existe desde la 3.6.19. Las versiones viejas de Android traen builds de SQLite más antiguos.
• INSERT OR REPLACE, OR IGNORE y la clave primaria TEXT que acepta NULL son comportamientos de SQLite. MySQL tiene REPLACE INTO (también borra y luego inserta) e INSERT … ON DUPLICATE KEY UPDATE; PostgreSQL tiene ON CONFLICT como SQLite (no ejecutado aquí).
• En PostgreSQL, un error dentro de una transacción aborta la transacción completa hasta que hagas rollback, a diferencia de SQLite (no ejecutado aquí).
• La medición usó una sola laptop con los datos en la caché del sistema operativo; la proporción de 3,600× depende del tamaño de la tabla hija (200,000 filas aquí) y crece con ella.

Ejercicios

Parte de la lista de alumnos tal como estaba justo antes de la sección del accidente (6 alumnos, claves foráneas activadas).

1. Agrega al alumno yuna08 (Yuna, nota 77) a la clase 1-B y luego dale 3 puntos extra a todos los de 1-B sin pasar de 100.
2. La escuela fusiona la clase 1-B con la 1-A. Con las claves foráneas activadas, DELETE FROM classes WHERE name = '1-B'; falla. Haz la fusión de forma segura.

Respuestas

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, y la clase quedó con Sora 94, Leo 58 y Yuna 80. min() con dos argumentos devuelve el menor, así que un 99 pasaría a 100, no a 102.

2. Mueve primero a los alumnos y luego borra la clase, todo en una transacción:

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

Nuestro intento de borrado directo imprimió FOREIGN KEY constraint failed (19). Después de la transacción, classes solo tenía 1-A, y los 7 alumnos estaban en la clase 1.

Siguiente clase

La clase 3 lee a través de varias tablas: JOIN, GROUP BY y HAVING, por qué COUNT(*) y COUNT(col) no coinciden, la "explosión de filas" que duplica tus totales, y si una subconsulta es más rápida que un JOIN (medido).

• Anterior: Curso de SQL 1/6: SELECT, WHERE, ORDER BY y NULL
• Siguiente: Curso de SQL 3/6: JOINs, GROUP BY y subconsultas (en inglés)
• Más adelante: índices y EXPLAIN QUERY PLAN (en inglés), transacciones, aislamiento y bloqueos (en inglés), funciones de ventana y CTE (en inglés)
• Dónde encaja: Fundamentos de programación para tu primer trabajo: ruta de estudio
• Escribir en una base de datos desde código de forma segura: PHP PDO y SQLite: inyección SQL, mostrada y corregida (en inglés)