SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[llanitedave]

On Saturday, November 22, 2014 10:32:30 PM UTC-8, Chris Angelico wrote:
> On Sun, Nov 23, 2014 at 5:08 PM, llanitedave wrote:
> > The application was working "correctly" earlier (meaning that I could enter and retrieve data with it; being a strictly user application it didn't allow deletes from the GUI), and then I discovered (while cleaning up the user documentation) that I'd neglected to include a couple of relatively important database fields.  Because of SQLite's limited ALTER TABLE capabilities, that mean I had to recreate the tables to add the fields, and in doing so noticed that the table in question didn't even have the foreign key constraint defined.  So ever since I defined that constraint, it hasn't let me save any records on that table from Python.  Although, as I said, when entering the same data through the Sqliteman application, it works fine. That's why I suspected that the problem might be in the Python API for SQLite3.
> >
> 
> Entirely possible. I never did track down the actual cause of the
> SQLite3 issues my students were having; though I suspect it's not
> purely a Python API issue. I tried to demonstrate the concept of
> foreign keys using the sqlite3 command line tool, and did a sequence
> of commands which ought to have failed, but didn't. Let's see if I can
> recreate this:
> 
> rosuav at sikorsky:~$ sqlite3
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table foo (val text primary key);
> sqlite> create table bar (val text references foo on delete set null);
> sqlite> insert into foo values ('asdf');
> sqlite> insert into bar values ('asdf');
> sqlite> insert into bar values ('qwer');
> sqlite> select * from foo;
> asdf
> sqlite> select * from bar;
> asdf
> qwer
> sqlite> delete from foo;
> sqlite> select * from foo;
> sqlite> select * from bar;
> asdf
> qwer
> 
> So the foreign key is being completely ignored. If I do the same
> commands in PostgreSQL, I get errors at appropriate places:
> 
> rosuav at sikorsky:~$ psql
> psql (9.3.5)
> Type "help" for help.
> 
> rosuav=> create table foo (val text primary key);
> CREATE TABLE
> rosuav=> create table bar (val text references foo on delete set null);
> CREATE TABLE
> rosuav=> insert into foo values ('asdf');
> INSERT 0 1
> rosuav=> insert into bar values ('asdf');
> INSERT 0 1
> rosuav=> insert into bar values ('qwer');
> ERROR:  insert or update on table "bar" violates foreign key
> constraint "bar_val_fkey"
> DETAIL:  Key (val)=(qwer) is not present in table "foo".
> rosuav=> select * from foo;
>  val
> ------
>  asdf
> (1 row)
> 
> rosuav=> select * from bar;
>  val
> ------
>  asdf
> (1 row)
> 
> rosuav=> delete from foo;
> DELETE 1
> rosuav=> select * from foo;
>  val
> -----
> (0 rows)
> 
> rosuav=> select * from bar;
>  val
> -----
> 
> (1 row)
> 
> 
> PostgreSQL is a lot more chatty, but what's significant here is that
> it won't let me insert into the referring table when there's no row in
> the referent. Also, when I delete the referred-to row, the referring
> row's key gets correctly set to NULL (like I specified in the
> constraint definition).
> 
> I don't know if there's a way to tell SQLite "hey, I want you to
> actually take notice of foreign keys, tyvm", as there's nothing
> obvious in the .help command output; but even if there is, I don't
> know why that isn't the default. Maybe there can be a way to say
> "ignore foreign key constraints for efficiency", but frankly, I'd
> rather have constraints actually checked - if you want to cheat them
> away, actually drop the constraints, don't have the database silently
> ignore them.
> 
> > As for Python3, that's a future possibility.  My next step was to expand the functionality of this particular app, which is intended for use in the field on a tablet or laptop, to a web-app using Django 1.7. WxPython was really a way to get my feet wet on it.  The Django version is using Python 3.4 and Postgresql 9.3.4, and it's still in the early stages -- I broke off of it to correct this mess.
> >
> > It's in the back of my head to go back to the field version at some point with Python3 and PyQt, but it is not this day.
> 
> Cool. There are several GUI toolkits for Python, and I know multiple
> of them do support Py3; I can't say which is the best, as I don't do
> my GUI programming in Python generally. But definitely try to use
> Python 3 if you can; and try to use PostgreSQL if you can, too.
> SQLite3 may be the light-weight option, but as you're seeing, it does
> sometimes take shortcuts; switching to a more full-featured database
> may be worth doing permanently, or at least for development (think of
> it like turning on a bunch of assertions).
> 
> > Anyway, if I can't get this thing straightened out, I may have to just remove the foreign key constraint and rely on application logic to ensure my data integrity.  :(
> >
> > I do appreciate the help, though Chris.  If nothing else, you've showed me some directions that I needed some extra learning in.
> 
> My pleasure! Databasing is well worth studying up on; the better laid
> out your table structure, the easier your coding will be - and more
> importantly, the easier your data debugging will be. A quick error
> message about a foreign key violation can save you hours or weeks of
> headaches down the track when you discover that, for the past year,
> you had two customers with account number 142857... and then find that
> there are many such pairs of duplicates, because your
> application-level code had a concurrency/race issue, and the database
> wasn't protecting you... This sounds contrived, but it's not
> unfeasible; I've seen some crazy problems in Pastel Accounting, which
> (back in the 1990s) used a non-SQL BTrieve back-end with terrible data
> integrity checking. My dad and I spent many hours wrestling with
> strange issues, and one of my favourite solutions was "Let's just
> import that into DB2 real quick, so we can do SQL queries on it". When
> your acchistl.dat file (that's Accounting, Invoice History, Lines - as
> opposed to acchisth.dat, which is invoice headers) is 256MB, you don't
> want to step manually through it.
> 
> Good luck with the project. If you need help, you know where to find us!
> 
> ChrisA

And thanks again, Chris! I'm not too worried about SQLite's limitations on this particular application, it's just an embedded single-user tool.  Workarounds should be fairly straightforward, as long as I know my own limitations!

For the Django version, it's going to be multi-user and I wouldn't really be comfortable using anything but Postgres.  But yeah, I'll need help, because I'm a slow learner!