Issues with Self-Referencing Foreign Key in SQLite

Question

I'm having trouble with a self-referencing foreign key in SQLite. I've created a table to store employee details, where each employee can have a manager who is also an employee. Here's the table definition:

CREATE TABLE Employees (
    Employee_ID INTEGER PRIMARY KEY,
    Name TEXT,
    Manager_ID INTEGER,
    FOREIGN KEY (Manager_ID) REFERENCES Employees(Employee_ID)
);

I've enabled foreign key constraints with PRAGMA foreign_keys = ON. However, when I insert data with a non-existent Manager_ID, SQLite does not raise an error. Here are the steps I've taken:

1. Enabled foreign key constraints:

PRAGMA foreign_keys = ON;

2. Created the table:

CREATE TABLE Employees (
    Employee_ID INTEGER PRIMARY KEY,
    Name TEXT,
    Manager_ID INTEGER,
    FOREIGN KEY (Manager_ID) REFERENCES Employees(Employee_ID)
);

3. Inserted data:

INSERT INTO Employees (Employee_ID, Name, Manager_ID) VALUES (1, 'Fitch', NULL);
INSERT INTO Employees (Employee_ID, Name, Manager_ID) VALUES (2, 'Alley', 1);
// This should fail because 9 doesn't exist, but doesn't:
INSERT INTO Employees (Employee_ID, Name, Manager_ID) VALUES (6, 'Blake', 9); 

Despite these steps, SQLite does not enforce the foreign key constraint for Manager_ID.

SELECT * FROM Employees

Gives:

Employee_ID	Name	Manager_ID
1	Fitch	NULL
2	Alley	1
6	Blake	9

Are Self-Referencing Foreign Keys supported in SQLite? If so, how do I get it to work?

Answer 1

Your code is correct, but as Alexander Patrov explained in the comments, you have to enable foreing key constraints each time you connect to the database.

Dbfiddle