Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #81345

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

Show key headers only | View raw

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

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 18:11 -0800
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 13:22 +1100
    Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 19:54 -0800
      Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Michael Torrie <torriem@gmail.com> - 2014-11-22 21:58 -0700
      Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 16:41 +1100
        Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 22:08 -0800
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 17:32 +1100
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 22:46 -0800
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Christian Gollwitzer <auriocus@gmx.de> - 2014-11-23 10:55 +0100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-23 10:21 +0200
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-23 17:29 -0800
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 19:31 +1100
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Jussi Piitulainen <jpiitula@ling.helsinki.fi> - 2014-11-23 11:40 +0200
              Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 20:53 +1100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-23 13:01 -0500
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-24 10:05 +0200
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-24 13:44 -0500
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-25 10:17 +1100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-25 07:31 +0200
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-25 17:40 +1100
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-23 11:58 -0500
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-23 18:22 -0800
    Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-24 15:49 +1100

csiph-web