In SQLite, you can use a PRAGMA statement to return a list of foreign keys for a given table.

Syntax

The syntax goes like this:

PRAGMA foreign_key_list(table-name);

Where table-name is the name of the table you want the list of foreign keys from.

Example

First, let’s create a table with a foreign key constraint.

CREATE TABLE Types( 
    TypeId INTEGER PRIMARY KEY, 
    Type
);

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(TypeId)
);

In this case I created two tables. The Pets table has a foreign key constraint that references the Types table.

Now I can use the PRAGMA foreign_key_list(table-name) statement to retrieve that foreign key.

.mode line
PRAGMA foreign_key_list(Pets);

Result (using vertical output):

      id = 0
      seq = 0
    table = Types
     from = TypeId
       to = TypeId
on_update = NO ACTION
on_delete = NO ACTION
    match = NONE

This PRAGMA statement returns eight columns, so I used .mode line to output the results vertically. This is so you don’t have to scroll sideways.

In this case there’s only one foreign key constraint on the table. If there were more, they would be listed in the results.

PRAGMA foreign_key_list(Types);

Return the CREATE TABLE Statements

The following statement can be used to return the actual SQL code used to create each table with a foreign key.

.mode column
SELECT sql 
FROM sqlite_master 
WHERE sql LIKE('%REFERENCES%');

Result:

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(TypeId)
)