SQLite list ALL foreign keys in a database

Question

Is there a way of listing ALL foreign keys in a SQLite database?

They don't seem to be stored in sqlite_master and PRAGMA foreign_key_list('table') only lists one at a time.

Alternatively, is there a way of listing what foreign keys reference a table?

Answer 1

It seems that all (or many) of the PRAGMA commands can be programatically selected with a little trick;

Usually the are called like:

PRAGMA table_info('my_table'); PRAGMA foreign_key_list('my_table'); 

But this can also be done:

SELECT * FROM pragma_table_info('my_table'); SELECT * FROM pragma_foreign_key_list('my_table'); 

And the schema can also be (more or less) obtained:

.schema pragma_table_info /* pragma_table_info(cid,name,type,"notnull",dflt_value,pk) */;  .schema pragma_foreign_key_list /* pragma_foreign_key_list(id,seq,"table","from","to",on_update,on_delete,"match") */ 

So, to get all the fks a JOIN between sqlite_master and pragma_foreign_key_list can do the trick:

SELECT      m.name     , p.* FROM     sqlite_master m     JOIN pragma_foreign_key_list(m.name) p ON m.name != p."table" WHERE m.type = 'table' ORDER BY m.name ; 

Just take care, that some fields of pragma_foreign_key_list like table, from, ... must be quoted;