according to this null foreign keys are allowed unless and until we are adding the appropriate "NOT NULL" constraint to the schema.
but I am seeing the some different behavior,
sqlite> PRAGMA Foreign_keys;
1
sqlite> create table proc (
sqlite> pid integer,
sqlite> name text,
sqlite> ppid integer,
sqlite> foreign key (ppid) references proc (id)
sqlite> );
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)
sqlite> 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)
sqlite> values (0, "init", null);
sqlite> select * from proc;
0|init|
how can I allow null foreign key in sqlite when PRAGMA foreign_keys=ON? or it is not possible at all?
CL. :
\nThe ID column is named pid, not id.\nThe parent key column must have a UNIQUE or PRIMARY KEY constraint.\n",
2013-04-30T17:13:51