SQLite外键不起作用 [英] Sqlite foreign key does not work

问题描述

我阅读了以前所有有关外键的问题.我做了所有可能的配置，但是我不能强制它正常工作. 即使类型表中没有任何记录，我也可以插入到节点表中.但是据我所知，外键约束不应允许这种情况发生.

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) <> '' )
)

和

CREATE TABLE "types" 
("id" INTEGER PRIMARY KEY  NOT NULL ,
"name" TEXT NOT NULL )

SQLite 3.19.3

SQLite 3.19.3

sqlite> PRAGMA foreign_keys;
foreign_keys
------------
1

sqlite> PRAGMA foreign_keys_list;
sqlite>

我不知道为什么PRAGMA foreign_keys_list答案为空.

I don't know why PRAGMA foreign_keys_list answer is empty.

此问题已解决，因为我在命令中使用了s
正确:PRAGMA foreign_key_list(nodes)

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

推荐答案

删除key之后的s —如果您输入错字并仅返回任何内容，SQlite不会警告您.

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

这篇关于SQLite外键不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！