Path: csiph.com!usenet.pasdenom.info!dedibox.gegeweb.org!gegeweb.eu!nntpfeed.proxad.net!proxad.net!feeder1-1.proxad.net!hq-usenetpeers.eweka.nl!hq-usenetpeers.eweka.nl!bcyclone03.am1.xlned.com!bcyclone03.am1.xlned.com!newsfeed.xs4all.nl!newsfeed4a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
MIME-Version: 1.0
In-Reply-To: <m4s5er$sg1$1@ger.gmane.org>
References: <ca987fb8-157d-4612-bafa-5da2da2920e0@googlegroups.com> <mailman.16153.1416709340.18130.python-list@python.org> <5d474a87-e2a5-43d3-b07c-4a1194880b4d@googlegroups.com> <54716987.7040301@gmail.com> <mailman.16159.1416721303.18130.python-list@python.org> <ea4e3af8-f173-4706-ba4e-4eca9d701e40@googlegroups.com> <CAPTjJmp4Y5zowWn5yFtJutKO4H5jVtqLaNtWqEpA6B35xNdsNA@mail.gmail.com> <m4s5er$sg1$1@ger.gmane.org>
Date: Sun, 23 Nov 2014 19:31:44 +1100
Subject: Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert
From: Chris Angelico <rosuav@gmail.com>
Cc: "python-list@python.org" <python-list@python.org>
Content-Type: text/plain; charset=UTF-8
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.16169.1416731513.18130.python-list@python.org>
Lines: 42
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:81345

On Sun, Nov 23, 2014 at 7:21 PM, Frank Millman <frank@chagford.com> wrote:
> "Chris Angelico" <rosuav@gmail.com> wrote in message
> news:CAPTjJmp4Y5zowWn5yFtJutKO4H5jVtqLaNtWqEpA6B35xNdsNA@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