In SQLite, foreign key constraints are not enforced unless foreign key support has been enabled.

Enabling foreign keys involves the following:

1. Enable foreign keys when compiling SQLite.
2. Enable foreign keys at runtime.

Enable Foreign Keys when Compiling SQLite

When it comes to compiling SQLite, it’s not so much a matter of enabling foreign keys – it’s more a matter of not disabling them.

Enabling foreign keys when compiling SQLite simply means that you don’t use SQLITE_OMIT_FOREIGN_KEY and SQLITE_OMIT_TRIGGER when compiling it.

If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced. The PRAGMA foreign_keys command is a no-op in this configuration.

If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error).

Enable Foreign Keys at Runtime

Even when the library has been compiled with foreign key constraints enabled, you still need to enable foreign key support at runtime.

You can do this with the following code:

PRAGMA foreign_keys = ON;

As with most PRAGMA statements, you could alternatively replace ON with TRUE, YES, or 1.

Once you’ve run that, your foreign keys will be enforced.

Note that this setting is not required for creating foreign keys, but it is required for enforcing foreign keys.

Other Connections

Note that this only enables foreign key constraints for the current database connection.

If you open a new connection, you’ll need to run that statement again if you want foreign keys to be enforced in that connection.

Check Foreign Key Support for the Current Connection

You can also check to see whether or not foreign keys have already been enabled for your current connection by running the following code.

PRAGMA foreign_keys;

Result:

1

In my case, I’ve already enabled foreign keys for this connection so the result is 1. If foreign keys were disabled, the result would be 0.

Here’s an example of disabling, checking the value, then enabling and rechecking the value.

PRAGMA foreign_keys = FALSE;
PRAGMA foreign_keys;
PRAGMA foreign_keys = YES;
PRAGMA foreign_keys;

Result:

sqlite> PRAGMA foreign_keys = FALSE;
sqlite> PRAGMA foreign_keys;
foreign_keys
------------
0           
sqlite> PRAGMA foreign_keys = YES;
sqlite> PRAGMA foreign_keys;
foreign_keys
------------
1