2014-03-14 12:00:00

Foreign Keys

(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)

I can't use SQLite! It doesn't support foreign keys!

Actually, it does. Since version 3.6.19, released in October 2009. Before the first iPad. Windows 7 was not quite out yet. Miley Cyrus was still Hannah Montana.

You're wrong! This code should fail but doesn't!

CREATE TABLE [to] (pk int PRIMARY KEY);
CREATE TABLE [from] (other REFERENCES [to](pk) );
INSERT INTO [from] (other) VALUES (3);

Fair enough. It is easy to get the impression that SQLite still lacks support for foreign keys because the feature is [usually] turned off by default, to preserve backward compatibility.

How to turn SQLite foreign key support on

To enable the enforcement of foreign key constraints, execute the following SQL statement, shortly after you open your SQLite database connection:

PRAGMA foreign_keys = 1;

I always use SQLite with foreign keys. In fact, I always open SQLite connections with a helper function which (1) opens the SQLite connection and (2) immediately executes the PRAGMA above.

Differences

Once you've got the feature turned on, foreign key support in SQLite is generally similar to what a SQL Server dev would expect. Full documentation is available on the SQLite website, but there are a few things worth highlighting.

SQLite supports simple foreign keys (with one column referencing one column), or composite foreign keys (with multiple columns referencing multiple columns).
SQLite does support ON DELETE and ON CASCADE.
SQLite allows a foreign key constraint to be deferred, which means that its enforcement is postponed until the transaction is committed. This is a handy feature that I really wish SQL Server had.

Shameless, blatantly self-serving commercial remark

Zumero for SQL Server automatically syncs foreign key constraints. Microsoft Sync Framework does not.

So there.

:-)

Summary

PRAGMA foreign_keys = 1; Always.