SQLite3 in Python 2.7 Rejecting Foreign Key Insert

Frank Millman frank at chagford.com
Mon Nov 24 09:05:11 CET 2014


"Dennis Lee Bieber" <wlfraed at ix.netcom.com> wrote in message 
news:8d747a5biq4rc559tvgju088508bp0o2gv at 4ax.com...
> On Sun, 23 Nov 2014 10:21:48 +0200, "Frank Millman" <frank at 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






More information about the Python-list mailing list