Does SQLite3 not support foreign key constraints?

Question

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.

Answer 1

SQLite Foreign Key Support

sqlite> PRAGMA foreign_keys = ON;

This will enable foreign key constraint.

Answer 2

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

Answer 3

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.

Answer 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?