Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.000 X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'essentially': 0.04; 'encoding': 0.05; 'subject:Python': 0.06; 'defaults': 0.07; 'explicit': 0.07; 'utf-8': 0.07; 'string': 0.09; 'absent': 0.09; 'naturally': 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; 'python': 0.11; 'stored': 0.12; 'anyway': 0.14; 'creates': 0.14; 'changes': 0.15; '(it': 0.16; '(possible': 0.16; '>the': 0.16; 'created.': 0.16; 'declaration': 0.16; 'inserting': 0.16; 'internally': 0.16; 'message-id:@4ax.com': 0.16; 'received:80.91.229.3': 0.16; 'received:plane.gmane.org': 0.16; 'sqlite': 0.16; 'thereby': 0.16; 'wxpython': 0.16; 'sat,': 0.16; 'integer': 0.24; 'necessary.': 0.24; 'text.': 0.24; 'url:home': 0.24; 'define': 0.26; 'tables': 0.26; 'primary': 0.26; 'gets': 0.27; 'header:X-Complaints-To:1': 0.27; 'related': 0.29; "doesn't": 0.30; 'field,': 0.30; "i'm": 0.30; "skip:' 10": 0.31; 'unique,': 0.31; 'text': 0.33; 'table': 0.34; 'maybe': 0.34; 'display': 0.35; 'test': 0.35; 'but': 0.35; 'set.': 0.36; 'charset :us-ascii': 0.36; 'application': 0.37; 'nov': 0.38; 'to:addr :python-list': 0.38; 'rather': 0.38; 'explain': 0.39; 'embedded': 0.39; 'structure': 0.39; 'to:addr:python.org': 0.39; 'received:org': 0.40; 'called': 0.40; 'space': 0.40; 'even': 0.60; 'field.': 0.61; 'save': 0.62; 'name': 0.63; 'field': 0.63; 'different': 0.65; 'therefore': 0.72; 'records': 0.73; 'so...': 0.84; 'visually': 0.84; 'received:108': 0.93 X-Injected-Via-Gmane: http://gmane.org/ To: python-list@python.org From: Dennis Lee Bieber Subject: Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Date: Sun, 23 Nov 2014 11:58:51 -0500 Organization: IISS Elusive Unicorn References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Gmane-NNTP-Posting-Host: adsl-108-79-223-115.dsl.klmzmi.sbcglobal.net X-Newsreader: Forte Agent 6.00/32.1186 X-No-Archive: YES 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: 31 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1416761942 news.xs4all.nl 2861 [2001:888:2000:d::a6]:40588 X-Complaints-To: abuse@xs4all.nl Path: csiph.com!usenet.pasdenom.info!bete-des-vosges.org!feed.ac-versailles.fr!nerim.net!novso.com!newsfeed.xs4all.nl!newsfeed3a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Xref: csiph.com comp.lang.python:81390 On Sat, 22 Nov 2014 18:11:10 -0800 (PST), llanitedave declaimed the following: >I've built a database in SQLite3 to be embedded into a python application using wxPython 2.8.12 and Python 2.7.6. I'm using Sqliteman to manage the database directly and make changes to the structure when necessary. > >One item that's been bugging me is when I'm inserting records into one particular table: > >The parent table is called "borehole", and the 'borehole_id' field is TEXT. It's essentially a name field, because every borehole name is naturally unique, therefore I thought an autoincrementing integer would be superfluous. > Absent an explicit declaration of an auto-increment integer primary key, SQLite implicitly creates a "rowid" that gets used internally anyway -- even if you don't see it in a "select * ..." result set. If you define an auto-increment integer primary key, the internal rowid is not created. So... You might as well use an explicit primary key, and thereby also save space in related tables as they only need to store an integer rather than some string field. Also note that SQLite3 defaults to using UTF-8 internally (it can be set for UTF-16BE/LE and maybe some others)... If one of your text strings was provided using a non-UTF-8 encoding and the other was (possible if different programs were used to process the data before insertion) they might visually display the same but be stored differently... Though that doesn't seem to explain your test "select". -- Wulfraed Dennis Lee Bieber AF6VN wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/