I am working against a SQLite3 database and I was surprised to be able to insert a line with a reference to another table in which the corresponding row with the foreign key does not exist.
The following minimal example illustrates the situation.
jdura@thinkpad:~/t$ sqlite3
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE CLIENTE( NOMBRE TEXT NOT NULL , CIUDAD TEXT,
...> PRIMARY KEY( NOMBRE));
sqlite> CREATE TABLE OTROS_DATOS( NOMBRE TEXT NOT NULL, CANTIDAD INTEGER ,
...> PRIMARY KEY( NOMBRE),
...> FOREIGN KEY(NOMBRE) REFERENCES CLIENTE(NOMBRE)
...> ON DELETE CASCADE
...> ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED);
sqlite> INSERT INTO CLIENTE( NOMBRE, CIUDAD) VALUES("JUAN", "CUZCO");
sqlite> SELECT * FROM CLIENTE;
JUAN|CUZCO
sqlite> INSERT INTO OTROS_DATOS( NOMBRE, CANTIDAD) VALUES( "JUAN", 7);
sqlite> SELECT * FROM OTROS_DATOS;
JUAN|7
sqlite> INSERT INTO OTROS_DATOS(NOMBRE, CANTIDAD) VALUES( "NO EXISTE", 27); /*
...> ^^^^ Esperaba fallo por referencia no satisfecha */;
sqlite> SELECT * FROM OTROS_DATOS; /* Pero la línea sí se ha insertado */;
JUAN|7
NO EXISTE|27
sqlite> INSERT INTO OTROS_DATOS(NOMBRE, CANTIDAD) VALUES( "NO EXISTE", 27); /*
...> ^^^^ Aquí sí que falla, al menos los cámpos únicos funcionan */;
SQL error: column NOMBRE is not unique
sqlite>
As I indicated in the comment, I expected that inserting a line in the table OTROS_DATOS
with reference to the client "NO EXISTE"
, which does not exist in the table, would fail CLIENTE
.
How do I have to create the tables so that referential integrity is respected?
sqlite has referential integrity disabled by default. You can enable it by running:
The enable is not persistent. You have to do it every time you connect to the database.