SQLitening Support Forum

I started using SQLitening/SQLite before SQLite offered native support for foreign keys.  As a consequence, I've got a ton of trigger code implemented to handle referential integrity for my foreign keys.  Today, I downloaded the latest SQLite DLL and started experimenting with the native support in the hopes I could eliminate all that trigger code.

I wrote the following test program:#COMPILE EXE
#DIM ALL

#INCLUDE ONCE "SQLitening.Inc"

FUNCTION PBMAIN () AS LONG

LOCAL I AS LONG, J AS LONG

LOCAL lSetNum AS LONG, lResult AS LONG
LOCAL sBuffer AS STRING, sTemp AS STRING

slOpen "fk-test.pdb", "C"

slExe "PRAGMA foreign_keys = ON; PRAGMA recursive_triggers = OFF;"

slExe "CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE, Tag, Desc, Prev_Pct_Comp, Cur_Pct_Comp, Prev_Mhs, Cur_Mhs, Inactive, DisplayOrder);" + _
"CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES JobPlans(RowID) ON DELETE CASCADE, " + _
  "UID UNIQUE NOT NULL, Desc, DisplayOrder, Type, Scope, OrigDur, CurDur, EstConf, OptimisticDur, PessimisticDur, PerComp DEFAULT 0, UID_Preds, UID_Succs, LogicProcessed);"


   slExe slBuildInsertOrUpdate( "JobPlans", "345351", "Name")

MSGBOX "Here we go..."

'   slExe slBuildInsertOrUpdate( "Tasks", "1" + $NUL + "ZZZ", "JobPlan_ID, UID")
slExe "INSERT INTO Tasks(JobPlan_ID,UID)VALUES(1,'ZZZ')"

MSGBOX "Done"

END FUNCTIONIt gives me an SQLite error stating foreign key mismatch.  When I open the fk-test.pdb database in SQLite Expert and execute the insert on the Tasks table (with the pragma set), it works without throwing an exception (unless I change the value for the JobPlan_ID field in which case it throws the exception as expected).

Any idea why the insert fails in my code (using SQLitening)?

Is SQLitening using sqlite3_prepare_v2() for the slExe function?  I think I'm still using SQLitening 1.3

You were getting "foreign key mismatch" error which normally means an
error in your creates.  Adding "RowID Integer Primary Key" seems to fix
that problem.  You will get "foreign key constraint error" if you try to
insert a row with invalid value. 
slExe "CREATE TABLE IF NOT EXISTS JobPlans (RowID Integer Primary Key,
Name UNIQUE, Tag, Desc, Prev_Pct_Comp, Cur_Pct_Comp, Prev_Mhs,
Cur_Mhs, Inactive, DisplayOrder)"Version 1.4 will automatically enable SQLite foreign key support.

Quote10. Changed slOpen to automatically enable SQLite foreign key support.
    SQLite disables this feature by default (for backwards compatibility),
    so must be enabled separately for each database connection. Also
    added the f ModChar to allow you to not enable foreign key support.

Why would I need to explicitly declare the RowID column/field?  That's added automatically by SQLite, isn't it?  I can see the column is there in SQLite Expert and the insert statement works from the command line, so it doesn't appear to be an SQLite issue.

I updated to 1.4 and am still seeing the same issue.

QuoteWhy would I need to explicitly declare the RowID column/field?

Don't know.  SQLite seems to complain if the column you use in the
references clause of the child table is not explicately declared (also
must have unique index) in the parent table. 

QuoteI updated to 1.4 and am still seeing the same issue.

Version 1.4 is unrelated to above.  Just wanted you to know you no longer
had to run the Pragma to activate foreign keys. 

I d/l'd and ran the SQLite command line application and it's showing the same error being thrown.  I can only guess that SQLite Expert is not changing the pragma value as expected and thus not enforcing the foreign key checking.  I'll submit the issue to the SQLite user group for discussion.  Doesn't look like this is an SQLitening issue.

I guess I'm not understanding your problem.  When I add the "RowID Integer
Primary Key" to your create statement it all works fine - no error.  What
exact error are you still getting? 

Fred, you understood the issue perfectly.  The 'problem' is that the behavior is not explicitly described in the SQLite documentation and I was concerned that using an alias for the RowID might involve some additional overhead or other performance issues. See here:

http://old.nabble.com/Using-foreign-key-reference-on-RowID-td30246958.html

Got it.  Yes, vacuum changing RowID values can be a problem.  I learned it
the hard way and now I normally create with a column of Integer Primary
Key. 

FYI - I've finished revamping my code to use SQLite's internal support for foreign keys and I'm seeing a 4-5x performance boost over my old code that enforced referential integrity using triggers.  This might be a function of how my database is structured, so YMMV, but I found it pretty significant.