Does SQLite3 not support foreign key constraints?

2019-01-01 16:39发布

问题:

I am have a problem with SQLITE3.

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>

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

It does not show any error.

How is this possible.

回答1:

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

PRAGMA foreign_keys = ON;

Otherwise SQLite will ignore all foreign key constraints.

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

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



回答2:

SQLite Foreign Key Support

sqlite> PRAGMA foreign_keys = ON;

This will enable foreign key constraint.



回答3:

Check out if you have foreign key constraints enabled in your SQLite: http://sqlite.org/foreignkeys.html#fk_enable



回答4:

Did you read the documentation? The main page says it\'s introduced with version 3.6.19. The link shows how to use foreign keys (Your code is correct).

Does your code give any error messages you omitted? Did you check all preconditions in specified in the documentation?