SQLite and Foreign Keys

While trying to build our latest database, I ran into a couple of problems with the foreign key support.   I eventually managed to create tables that had a foreign key with an ON DELETE CASCADE attribute applied.

This in itself wasn’t easy.  The table designer doesn’t seem to allow you to change anything to do with the deletes or updates, so you need to run a command that alters your table (or drops it and recreates it with the correct constraints).

I did that by going to the design view, and choosing

Generate Change Script…

then edited the script to append ON DELETE CASCADE to the relevant CONSTRAINT lines.

Having succeeded with that, I went to my sample data I’ve been playing with and deleted one, only to find that the related records were orphaned in the database!

What’s going on?  Why don’t the foreign key constraints in SQLite work?

A little bit of searching turns up that you have to actually turn on foreign key support!  This is done by executing

PRAGMA foreign_keys = ON

statement for every connection.

So I tried this in a SQL window in the designer, every time I tried to turn it on with a PRAGMA command it wouldn’t stick.  Of course:  It’s creating a new connection!

The secret then is to alter your connection string to include

foreign keys=True;

at the beginning of it.

Et Voila!  It now works.

This was a result of the VS201X designer opening a new connection each time.