SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

On Sun, Nov 23, 2014 at 7:21 PM, Frank Millman <frank at chagford.com> wrote:
> "Chris Angelico" <rosuav at gmail.com> wrote in message
> news:CAPTjJmp4Y5zowWn5yFtJutKO4H5jVtqLaNtWqEpA6B35xNdsNA at mail.gmail.com...
>>
>> Entirely possible. I never did track down the actual cause of the
>> SQLite3 issues my students were having; though I suspect it's not
>> purely a Python API issue. I tried to demonstrate the concept of
>> foreign keys using the sqlite3 command line tool, and did a sequence
>> of commands which ought to have failed, but didn't.
>
> The default is for sqlite3 to ignore foreign key contraints.
>
> To enable them, add the following -
>
> pragma foreign_keys = on;
>
> It works for me.

Thanks, I went poking around briefly but didn't find that pragma. I
still stand by my view, though, that constraint checking should be
active by default; you have to explicitly create them anyway, so it's
not like you'd unexpectedly lose heaps of performance. And even if it
is costly, the default settings should be for reliability, more than
performance; if you want to cut corners to speed things up, that's
your decision, but that should be something you have to explicitly ask
for.

It'd be nice to have a warning or at least informational: "Foreign key
constraint created, but constraint checking is inactive". Would mean
folks like me, who grew up on IBM's DB2 and now use PostgreSQL,
wouldn't be so confused by "why did that not throw an error".

> Unfortunately it has a limitation, which they acknowledge but they say is
> unlikely to be addressed. You can access more than one database concurrently
> by using the 'attach' command, and qualifying a remote tablename as
> {database} dot {tablename}. You can then include the remote table in any sql
> command. However, it will not enforce foreign key constraints across
> databases.

That's more of an edge case; I wouldn't be too bothered by that.

ChrisA