Sqlite foreign key does not work

2019-08-29 09:45发布

问题:

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

回答1:

Remove that s after key — SQlite won't warn you if you made a typo and simply returns nothing.



回答2:

The syntax is PRAGMA foreign_key_list(table-name);