Geek Answers Handbook

SQLite List All Foreign Keys in the Database

Is there a way to list all foreign keys in a SQLite database?

They do not seem to be stored in sqlite_master, and PRAGMA foreign_key_list('table') displayed only one at a time.

Alternatively, is there a way to indicate which foreign keys reference the table?

+10
sqlite database-design sqlite3
source share
2 answers

With the SQLite shell, use the .schema and use GREP to filter rows containing REFERENCES .

From shell.c in the SQLite repository, today's version in trunk, two queries:

 SELECT sql FROM ( SELECT sql sql, type type, tbl_name tbl_name, name name FROM sqlite_master UNION ALL SELECT sql, type, tbl_name, name FROM sqlite_temp_master ) WHERE tbl_name LIKE shellstatic() AND type != 'meta' AND sql NOTNULL ORDER BY substr(type, 2, 1), name

and

 SELECT sql FROM ( SELECT sql sql, type type, tbl_name tbl_name, name name FROM sqlite_master UNION ALL SELECT sql, type, tbl_name, name FROM sqlite_temp_master ) WHERE type != 'meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%' ORDER BY substr(type, 2, 1), name

The second option is what you are looking for.

+15
source share

This is a hack, but it works for us:

  • using your favorite text editor, search your database ( *.db ) for the following: CONSTRAINT

Example

NotePad ++ => Search => Find in Files => Find What = CONSTRAINT

0
source share

More articles:


All Articles