I am using Database.InsertRecord and RecordSet.Edit/Update to manage Child Tables and a foreign key field… But every time I try to do either (I always assign all fields as this is is for a UI editing the table and I don;t want to track which fields have changed)
But every time I try to do a save or update I get a foreign key constraint violation… where as far as I can see there should be none…
I know I’ve done this before …, but it may have been using SQL directly (not a good option here with a lot fields) or maybe before foreign keys were enforced in SQLite.
Has anyone see this? If No does anyone have an idea where I should look for an issue?
BTW for what it’s worth I can exactly the same record in Valentina Studio without a foreign key violation with teh same parent key value without getting a foreign key violation.
Added:
Just converted one case to do the insert and update via SQL and it works fine … so unless I am doing something stupid the Xojo functions don’t work with foreign keys… This is frustrating when I have to get this done ASAP!!!
Read the sqlite docs - there are several modes in which constraints get checked (and you can change them with pragma commands). If you use the mode where a check is made immediately, you may run into the case where you’d have to make two changes to keep it valid, but it already checks in between, which you don’t want. I believe there’s a pragma that lets you change that behavior.
At worst you could just turn the checks off entirely and then issue pragma statements to perform checking explicitly.
And I do use foreign key constraints with Xojo, so I got it working. Though, I’ve done that only with the old REALSQLDatabase, not the newer SQLiteDatabase, so I can’t assure you that it’s working there, too, but it should.
But as much as hassle it is o rewrite I am rewriting to do the update and inserts using DB.SQLExecute . That works without needing pragmas (which is how it should be with the Xojo framework too…).
I think while doing it that way is a pain, the code will easier to maintain than having bunch of special logic.
BTW, be careful when using SQL cmd directly - make sure you’re escaping the user-provided strings that you insert into the cmd, with at least a arg.ReplaceAll("'", "''").
It better even, use the PreparedStatement for that.
if you insert into a table that has a foreign key constraint on it BEFORE the foreign rows exist you’ll have problems
Say you have an invoice header and invoice details tables and the invoice details has a foreign key to the invoice header (on invoice ID). If you try to insert the invoice details before the invoice header then you’ll get foreign key constraint errors because the rows the foreign key refers to don’t exist
Norman, there are cases where simply a “correct order” is not working to prevent such a foreign key violation. I had that case myself, where there were intertwined relationships (could might call it a circular reference), IIRC.
[quote=292414:@Norman Palardy]if you insert into a table that has a foreign key constraint on it BEFORE the foreign rows exist you’ll have problems
Say you have an invoice header and invoice details tables and the invoice details has a foreign key to the invoice header (on invoice ID). If you try to insert the invoice details before the invoice header then you’ll get foreign key constraint errors because the rows the foreign key refers to don’t exist[/quote]
I am picking the foreign key from a popup menu loaded from the DB. Even if Iw as somehow messing that up, what you suggest can not be the issue as in exactly the same place, using DB.SQLExecute works just fine without a foreign key violation.
Either there is some other problem in my Xojo code (which I can’t see) that only affects RecordSetUp and DB.insert, or there is some issue in what the Xojo DB classes are doing under the hood in some cases.
[quote=292410:@Thomas Tempelmann]BTW, be careful when using SQL cmd directly - make sure you’re escaping the user-provided strings that you insert into the cmd, with at least a arg.ReplaceAll("'", "''").
It better even, use the PreparedStatement for that.[/quote]
You make a good point… I thought of using prepared statements but I was worried that I would run into the same foreign key constraint violation with Xojo prepared statement. Since I am under time pressure I went straight to raw SQL which I was sure would work, if I was not doing something basically wrong with the Xojo code.
[quote=292422:@Norman Palardy]you don’t have circular foreign key constraints (which suggests your db is not well normalized)
you insert “parent” rows before “detail” rows (so the detail rows foreign key constraint can be satisfied)
[/quote]
Please define what you mean by that.
I one case ( but not true for the others ) I have the Foreign key be the key0 for that table (but Not the same record) . I’ve done that in various DB’s over the last 30 years without an issue. In this case it makes sense for the physical process I am storing info on.
But in any case, As I said when I use raw SQL in EXACTLY the same place with the same data I do NOT get a foreign key constraint violation, so it is hard for me to see how 1 or 2 could be the issue.
table InvoiceHeader
id integer
... other invoice header details like date etc
end table
table InvoiceDetails
id integer
invoiceId integer <<<< this is the foreign key defined with
... other invoice line details like SKU, qty, current unit price, etc
end table
then the “parent” is the invoice header row for an invoice and the “details” are the invoice detail rows
sqlite> create table InvoiceHeader ( id integer primary key ) ;
sqlite> create table invoiceDetail ( id integer primary key, invoice_id integer references InvoiceHeader(id) ) ;
# note NO foreign key enforcement on
sqlite> insert into invoicedetail (id , invoice_id ) values ( 1 , 1 ) ;
# note NO WARNING
sqlite> delete from invoicedetail ;
sqlite> pragma foreign_keys = true ;
sqlite> insert into invoicedetail (id , invoice_id ) values ( 1 , 1 ) ;
Error: FOREIGN KEY constraint failed
Now you could turn off foreign key checks as you insert - but I’d say this is a bad thing to do