SQLite3 不支持外键约束吗?

2021-12-05 00:00:00 sql database sqlite

我在使用 SQLITE3 时遇到问题.

I am have a problem with SQLITE3.

我使用以下 SQL 脚本创建了 2 个表 personsorders:

I have created 2 tables persons and orders using the following SQL script:

sqlite> create table Persons(
            P_Id int primary key,
            LastName varchar,
            FirstName varchar,
            Address varchar,
            City varchar
        );

sqlite> create table Orders(
            O_Id int NOT NULL,
            OrderNo int NOT NULL,
            P_Id int, 
            PRIMARY KEY (O_Id),
            FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
        );

sqlite> insert into Orders values(1,77895,3);

sqlite> select * from Orders;

1|77895|3

sqlite>

即使persons表是空行也可以插入到orders表中.

Even though the persons table is empty rows can be inserted into the orders table.

它没有显示任何错误.

这怎么可能.

推荐答案

在 SQLite 3.x 中,您必须每次连接到 SQLite 数据库时进行以下查询:

In SQLite 3.x, you have to make the following query every time you connect to an SQLite database:

PRAGMA foreign_keys = ON;

否则 SQLite 将忽略所有外键约束.

Otherwise SQLite will ignore all foreign key constraints.

为什么每次都这样?根据文档,向后兼容 SQLite 2.x.

Why every time? Backwards compatibility with SQLite 2.x, according to the the documentation.

在 SQLite 4.x 中,FK 约束将默认启用.

In SQLite 4.x, FK constraints will be enabled by default.

相关文章