SQLite includes a PRAGMA statement that allows you to check for foreign key violations on a whole database or a given table.

The statement is PRAGMA foreign_key_check, and it works as follows.

Syntax

You can use it in one of two ways:

PRAGMA schema.foreign_key_check;
PRAGMA schema.foreign_key_check(table-name);

The first line checks the whole database, while the second one checks just a specific table.

The optional schema argument specifies the name of an attached database or main or temp for the main and the TEMP databases. If schema is omitted, main is assumed.

Example

Let’s create two tables with a relationship between them.

In this case, the Pets table has a foreign key that references the TypeId column on the Types table.

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

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

Now let’s enter data that violates the foreign key constraint.

PRAGMA foreign_keys = OFF;

INSERT INTO Types VALUES 
    ( 1, 'Dog' ),
    ( 2, 'Cat' );

INSERT INTO Pets VALUES 
    ( 1, 'Homer', 3 );

The second INSERT statement violates the foreign key constraint. This is because it inserts a value of 3 into the Pets.TypeId column, when there isn’t a corresponding value in the Types.TypeId column.

An important thing to note here is that I explicitly disabled foreign keys by using PRAGMA foreign_keys = OFF. This is the default setting in SQLite, but I wanted to make it clear for this example.

Now let’s check the database for foreign key violations.

PRAGMA foreign_key_check;

Result:

table       rowid       parent      fkid      
----------  ----------  ----------  ----------
Pets        1           Types       0         

This tells us that the Pets table has a foreign key violation on the row with a ROWID of 1. It also tells us the name of the parent table, as well as the foreign key ID.

Let’s add more data to the Pets table and run the check again. The first two lines adhere to the foreign key, but the last line doesn’t.

INSERT INTO Pets VALUES 
    ( NULL, 'Yelp', 1 ),
    ( NULL, 'Fluff', 2 ),
    ( NULL, 'Brush', 4 );

PRAGMA foreign_key_check;

Result:

table       rowid       parent      fkid      
----------  ----------  ----------  ----------
Pets        1           Types       0         
Pets        4           Types       0         

We now have two rows returned when checking the whole database for foreign key violations.

Check a Specific Table

You can also specify a table to run the check against.

Here’s an example of re-writing the previous check to specify just the Pets table.

PRAGMA foreign_key_check(Pets);

Result:

table       rowid       parent      fkid      
----------  ----------  ----------  ----------
Pets        1           Types       0         
Pets        4           Types       0         

Same result.

Here’s the result if I specify the other table.

PRAGMA foreign_key_check(Types);

Result:

(It’s blank because there are no results.)

Specify a Schema

As mentioned, you can also specify the schema.

PRAGMA main.foreign_key_check(Pets);

Result:

table       rowid       parent      fkid      
----------  ----------  ----------  ----------
Pets        1           Types       0         
Pets        4           Types       0         

In my case I used the main database, but you could replace main with the name of your attached database.

How to Enforce Foreign Keys

As mentioned, SQLite doesn’t enforce foreign keys unless you explicitly specify that they should be enforced.

You can enforce foreign keys using PRAGMA foreign_keys = ON.

See How to Enable Foreign Key Support in SQLite for more information and examples.