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

Groups > comp.lang.python > #81440

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

Show key headers only | View raw

"Dennis Lee Bieber" <wlfraed@ix.netcom.com> wrote in message 
news:8d747a5biq4rc559tvgju088508bp0o2gv@4ax.com...
> On Sun, 23 Nov 2014 10:21:48 +0200, "Frank Millman" <frank@chagford.com>
> declaimed the following:
>
>>
>>
>>The default is for sqlite3 to ignore foreign key contraints.
>>
[...]
>>
>>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.
>>
> Seems acceptable-- as such enforcement would require it to
> automatically reattach (likely in both directions) any/all attached
> databases when a change is made. Imagine the thrashing if one has some
> master database in a read-only mode for most users, and each user has some
> child database referencing some field in the master... And the owner of 
> the
> master database deletes a record...
>
> Are they suddenly going to have to have update capability on every
> user's database to check/enforce multi-db references?

I will explain why such a feature would have been useful for me.

My accounting system handles multiple companies. I needed to figure out a 
way to keep each one's data separate from the others.

Originally I supported two databases - PostgreSQL and Sql Server. They both 
have a concept called 'schemas', which handles my requirement elegantly - 
each company's data is stored in its own schema.

When I decided to add sqlite3 as a third option, I found that they did not 
have 'schemas'. However, I could get very close by creating multiple 
databases and using 'attach' to access them from one central database.

Overall I have been very impressed with how 'complete' sqlite3 is. The 
limitation mentioned above is one of the few compromises I have had to make. 
The sql syntax used by PostgreSQL and sqlite3 is virtually identical. I 
found more variations between PostgreSQL and Sql Server.

Frank

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