Foreign key isn't being enforced

Question

Why is the following foreign key constraint (although executes fine) not enforced by SQLite? How can I go about enforcing the relationship?

CREATE TABLE User (
    UserID TEXT Unique NOT NULL PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Username TEXT NOT NULL,
    Password TEXT NOT NULL,
    Email TEXT NOT NULL,
    SignupDate TEXT NOT NULL
)

CREATE TABLE Category (
    CategoryID TEXT Unique NOT NULL PRIMARY KEY,
    UserID TEXT,
    FOREIGN KEY(UserID) REFERENCES User(UserID)
)

Margotmargrave

Answer 1

As the relevant docs say (in section 2. Enabling Foreign Key Support):

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.

Have you used that PRAGMA in the relevant connection? (Assuming, as the docs say, that sqlite is compiled appropriately, and also a recent-enough version to offer foreign key constraint enforcement, of course).

Vaticinal

Thank you so much for the prompt response. I never noticed that in the documentation (will look harder next time). Having just attempted to run that command I receive the following error: "SQLite prepare() failed. ERROR: authorization denied DETAILS: not authorized EXPRESSION: PRAGMA foreign_keys = ON;" I am assuming either the version they are using doesnt support it or has had the functionality disabled. Could I create the same end result using triggers? If so could anyone please provide some example trigger syntax for SQLITE? – Margotmargrave

@Maxim, I believe sqlite foreign key triggers require exactly the same underlying functionality as plain old foreign keys do (often removed in embedded builds of sqlite that want to be small and fast more than they want to offer "full relational ACID power" at substantial performance and footprint costs). – Vaticinal

It is important to repeat what Alex says: the PRAGMA needs to be activated for the relevant connection. Which means it is not sufficient to do it only once on creating the shema. – Dundalk

Answer 2

You can also turn on Foreign Key support via embedding in connectionstring:

foreign keys=True

Example:

"Data Source={DatabaseFullFilePath};Version=3;foreign keys=True;datetimeformat=CurrentCulture"

Plosion

Note to the reader: this is for ODBC connection strings, not for sqlite's own uri filenames. As of 2022, sqlite does not support enabling FKs directly via a uri filename. – Saxon