Does SQLite support referential integrity?

Question

I have a Django web-server configured to use SQLite.

In a many-to-many relationship (with extra fields), Django forces me to use the relationship model to establish a relationship between two objects. But I'm able to create a relationship between objects, that don't yet exist in the related tables.

For e.g:

I have table1 and table2 which are related via table12.
In table1, there is just one object called A.
In table2, there is just one object called X.
I can create a record in table12 that depict a relationship between A & Y; even though Y doesn't exist in table2.

My relationship model has marked the foreign keys appropriately.

Dorsoventral

Answer 1

SQLite does not enforce foreign key constraints by default (mostly to be backward compatible).

To enable it, you have to execute

PRAGMA foreign_keys = 1

after you connected to the database.

See the manual about PRAGMA foreign_keys for more details.

Janeljanela

Also found a way to enable it in Django! #6746263 – Dorsoventral

A slightly nicer variant would be PRAGMA foreign_keys=ON rather than 1 – Contradance