SQLite3 does not fail to insert nonexistent reference

4

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?

    
asked by Jose Antonio Dura Olmos 07.01.2016 в 09:17
source

1 answer

9

sqlite has default referential integrity disabled. You can enable it by running:

sqlite> PRAGMA foreign_keys=ON

Enabling is not persistent. You have to do it every time that connects to the database.

    
answered by 07.01.2016 / 10:07
source