Is it allowed to have null foreign key in sqlite when PRAGMA foreign_keys=ON?
android-sqlite, sqlite
Solution
- The ID column is named `pid`, not `id`.
- The parent key column must have a `UNIQUE` or `PRIMARY KEY` constraint.
Problem
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?