Is it allowed to have a null foreign key in sqlite when PRAGMA foreign_keys = ON?

Question

according to this null foreign keys are allowed until we add the corresponding "NOT NULL" constraint to the schema.

but I see a different behavior

sqlite> PRAGMA Foreign_keys; 1 sqlite> create table proc (pid integer, name text, ppid integer, foreign key (ppid) references proc (id)); sqlite> .schema proc CREATE TABLE proc (pid integer, name text, ppid integer, foreign key (ppid) references proc (id)); sqlite> insert into proc (pid, name, ppid) values (0, "init", null); Error: foreign key mismatch sqlite> PRAGMA Foreign_keys=OFF; sqlite> PRAGMA Foreign_keys; 0 sqlite> insert into proc (pid, name, ppid) values (0, "init", null) sqlite> select * from proc; 0|init| 

how can i enable null foreign key in sqlite when PRAGMA foreign_keys = ON? or is it even impossible?

Answer 1

• The identity column is named pid , not id .
• The parent key column must have a UNIQUE or PRIMARY KEY .

Answer 2

Try adding a foreign key clause by changing the table creation statement to:

 CREATE TABLE proc (pid integer, name text, ppid integer, foreign key (ppid) references proc (id) ON UPDATE CASCADE ON DELETE SET NULL);