SQLite3 in Python 2.7 Rejecting Foreign Key Insert
Frank Millman
frank at chagford.com
Mon Nov 24 03:05:11 EST 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