I am working against a database in SQLite3 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 minimum 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 it would fail to insert a line in table OTROS_DATOS
with reference to client "NO EXISTE"
, which does not exist in table CLIENTE
.
How do I have to create the tables so that the referential integrity is respected?