Path: csiph.com!usenet.pasdenom.info!news.albasani.net!news.stack.nl!newsfeed.xs4all.nl!newsfeed1a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.003 X-Spam-Evidence: '*H*': 0.99; '*S*': 0.00; 'syntax': 0.04; 'subject:Python': 0.06; 'made.': 0.07; 'postgresql': 0.07; 'suddenly': 0.07; 'users,': 0.07; 'option,': 0.09; 'read-only': 0.09; 'received:80.91': 0.09; 'received:80.91.229': 0.09; 'received:gmane.org': 0.09; 'received:list': 0.09; 'subject:2.7': 0.09; 'stored': 0.12; 'wrote': 0.14; 'command,': 0.16; 'deletes': 0.16; 'qualifying': 0.16; 'received:80.91.229.3': 0.16; 'received:plane.gmane.org': 0.16; 'referencing': 0.16; 'skip:n 50': 0.16; 'variations': 0.16; 'ignore': 0.16; 'seems': 0.21; 'handles': 0.22; 'separate': 0.22; 'creating': 0.23; 'server.': 0.24; 'supported': 0.26; 'header:X-Complaints-To:1': 0.27; 'feature': 0.29; 'addressed.': 0.29; 'mode': 0.30; 'originally': 0.30; "company's": 0.31; 'enforce': 0.31; "user's": 0.31; 'figure': 0.32; 'limitation': 0.33; 'third': 0.33; 'table': 0.34; 'could': 0.34; 'requirement': 0.35; 'but': 0.35; 'add': 0.35; '+0200,': 0.36; 'skip:> 10': 0.36; 'useful': 0.36; 'two': 0.37; 'remote': 0.38; 'nov': 0.38; 'others.': 0.38; 'needed': 0.38; 'to:addr:python-list': 0.38; 'explain': 0.39; 'to:addr:python.org': 0.39; 'received:org': 0.40; 'called': 0.40; 'how': 0.40; 'is.': 0.60; 'most': 0.60; 'mentioned': 0.61; 'such': 0.63; 'field': 0.63; 'decided': 0.64; 'central': 0.64; 'more': 0.64; 'between': 0.67; 'close': 0.67; 'frank': 0.68; 'default': 0.69; 'overall': 0.69; 'foreign': 0.74; 'capability': 0.84; 'enforcement': 0.84; 'acknowledge': 0.93; 'imagine': 0.93 X-Injected-Via-Gmane: http://gmane.org/ To: python-list@python.org From: "Frank Millman" Subject: Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Date: Mon, 24 Nov 2014 10:05:11 +0200 References: <5d474a87-e2a5-43d3-b07c-4a1194880b4d@googlegroups.com> <54716987.7040301@gmail.com> <8d747a5biq4rc559tvgju088508bp0o2gv@4ax.com> X-Gmane-NNTP-Posting-Host: 197.89.14.153 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.3790.4657 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4913 X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.15 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 54 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1416816326 news.xs4all.nl 2949 [2001:888:2000:d::a6]:54575 X-Complaints-To: abuse@xs4all.nl Xref: csiph.com comp.lang.python:81440 "Dennis Lee Bieber" wrote in message news:8d747a5biq4rc559tvgju088508bp0o2gv@4ax.com... > On Sun, 23 Nov 2014 10:21:48 +0200, "Frank Millman" > 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