detalles útiles programando con sqlite3

1. foreign_keys

Por defecto, por razones de compatibilidad con versiones anteriores, el soporte a foreign_keys constraints está deshabilitado, a no ser que se compile de otra manera la librería expresamente. Sin embargo, en tiempo de ejecución también puede habilitarse, ejecutando pragma foreign_keys = on.

Cuando está deshabilitado, se acepta la sintaxis de referencias externas, pero realmente no se utiliza, por lo que no sirve de nada: se puede eliminar una fila que esté referida desde otro sitio sin ningún problema, por lo que, excepto cuando se está usando para tareas triviales, conviene habilitar el soporte.

Sin embargo, hay un detalle que puede dar mucha guerra: la corrección de las declaraciones de referencias externas se comprueba solo parcialmente al crear las tablas; el resto se hace cada vez que se va a ejecutar una operación sobre los datos implicados, incluído algo tan simple como comprobar que la(s) columna(s) referida(s) existen en la tabla referida.

Por añadidura, el mensaje que produce cuando existe un problema que le impide realizar la operación no ayuda en absoluto a localizar la causa: foreign key mismatch; no vale de nada examinar la coherencia de los datos con los que se intenta la operación porque el problema no está en ellos, sino en la definición de alguna referencia externa (de hecho, si se depura con cuidado puede verse que el fallo no ocurre al ejecutar la orden, sino al prepararla).

Y por mas añadidura, ocurre que puede modificarse una columna en una tabla implicada en una definición errónea de referencia externa siempre que esa columna no esté implicada en cualquier referencia externa, pero no si lo está, aunque la referencia en que lo está sea correcta. Por ejemplo, si en la tabla A hay una columna x, que no tiene explícita o implícitamente la consideración de UNIQUE está, erróneamente, referida desde otra tabla B, entonces actualizar la columna y de la misma tabla A no implicada en referencia externa alguna es posible, mientras que hacerlo con su columna z, que es referencia a una tercera tabla C producirá el consabido maldito error foreign key mismatch.

Detalles de la documentación de sqlite aquí.