Output of the SQLite's foreign_key_list pragma

Question

Using SQLite3 with the following schema:

CREATE TABLE Customers(ID INTEGER PRIMARY KEY, Company TEXT NOT NULL UNIQUE, Country TEXT NOT NULL, City TEXT NOT NULL);
CREATE TABLE Orders(ID INTEGER PRIMARY KEY, CustomerID INTEGER NOT NULL, FOREIGN KEY(CustomerID) REFERENCES Customers(ID) ON DELETE RESTRICT ON UPDATE RESTRICT);

and issuing this command:

PRAGMA foreign_key_list(Orders);

results in the following output:

0|0|Customers|CustomerID|ID|RESTRICT|RESTRICT|NONE

As the documentation says nothing about the meaning of the output of this pragma, apart from the obvious (Customers - Parent table, CustomerID - Child key, ID - Parent key, RESTRICT - ON DELETE and the second RESTRICT - ON UPDATE) I presume that NONE coresponds to the unsupported MATCH clause.

The thing which I can't figure out by myself is the meaning of the first two zeros. Could someone tell me what it is?

Answer 1

The output of PRAGMA foreign_key_list() consists of following columns in order -

id, seq, table, from, to, on_update, on_delete, match

So, in the output you got the first two 0s are for id and seq.

Take below example executed in sqlite3 cli with header and column option on -

CREATE TABLE Test (first INTEGER, second INTEGER, FOREIGN KEY (first) REFERENCES A(a) ON DELETE CASCADE, FOREIGN KEY (second) REFERENCES B(x) ON DELETE CASCADE);
sqlite> 
sqlite> PRAGMA foreign_key_list(Test);
id          seq         table       from        to          on_update   on_delete   match     
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           0           B           second      x           NO ACTION   CASCADE     NONE      
1           0           A           first       a           NO ACTION   CASCADE     NONE 

I know the table Test look like a nightmare, but just ignore the horrible schema for a moment.

You can see there are two foreign keys in table Test and so there are two entries shown in PRAGMA foreign_key_list() output. You can see the id fields value are 0 and 1 respectively but the seq values are all 0.

As you may know sqlite allows multiple column names in foreign key statement. So if you take the next example -

sqlite> CREATE TABLE Test2 (first INTEGER, second INTEGER, FOREIGN KEY (first, second) REFERENCES A(a, b) ON DELETE CASCADE);
sqlite> 
sqlite> PRAGMA foreign_key_list(Test2);
id          seq         table       from        to          on_update   on_delete   match     
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           0           A           first       a           NO ACTION   CASCADE     NONE      
0           1           A           second      b           NO ACTION   CASCADE     NONE  

So, the multiple column key results in multiple rows in the output where id is same as this is actually one key. But the seq value differs as there are multiple columns in the key.