I read all the previous issues with the foreign keys. I did all the possible configurations but I can not enforce it to work. I can insert to the nodes table even if I don't have any record in the types table. But as I know the foreign key constraint should not allow this to happen.
CREATE TABLE nodes(
id int NOT NULL PRIMARY KEY ,
ver int NOT NULL,
lock int NOT NULL,
title varchar(50) NOT NULL,
typeid int NOT NULL REFERENCES types(id),
desc text NOT NULL,
CHECK(trim(id) <> '' AND trim(ver) <> '' AND trim(lock) <>'' AND trim(title)
<> '' AND trim(typeid) <> '' AND trim(desc) <> '' )
)
and
CREATE TABLE "types"
("id" INTEGER PRIMARY KEY NOT NULL ,
"name" TEXT NOT NULL )
SQLite 3.19.3
sqlite> PRAGMA foreign_keys;
foreign_keys
------------
1
sqlite> PRAGMA foreign_keys_list;
sqlite>
I don't know why PRAGMA foreign_keys_list
answer is empty.
It is solved because I used s
in my command
Correct : PRAGMA foreign_key_list(nodes)
sqlite> PRAGMA foreign_key_list(nodes);
id seq table from to on_update on_delete match
---------- ---------- ---------- ---------- ---------- ---------- -------- ------------
0 0 types typeid id NO ACTION NO ACTION NONE