SQLite Foreign Key problem

Databases
#1

SQLite database with: Pragma foreign_Keys = ON

Table tbl_Countries (fld_ShortName (primary key), fld_LongName) (eg. ‘BE’, ‘Belgium’)

Table tbl_Cities (fld_Country (primary key), fld_Zip (primary key), fld_City)
Foreign key definition: fld_Country references tbl_Countries(fld_ShortName) On Delete Restrict (eg. ‘BE’, ‘2000’, ‘Brussels’)

This piece of code doen’t catch a constraint error (eg. Remove country = ‘BE’)

#Pragma BreakOnExceptions False

Var sSQL As String
sSQL = "DELETE FROM tbl_Countries WHERE  fld_shortName = 'BE'"
Try
  db.ExecuteSQL(sSQL)
  MessageBox ("Deleted")
Catch err As DatabaseException
  MessageDialog.Show("Fout " + err.ErrorNumber.ToString)
End Try

#Pragma BreakOnExceptions True

Any idea why this delete error is not detected?

#2

Hi @Carlos_De_Backer

Commenting out your first line of code makes any difference?

#3

No change… Record is removed without error.

#4

@Javier_Menéndez

In DB Browser for SQLite you get an error when you try to delete the record in tbl_Countries (screen print).

Screenshot 2021-03-29 at 14.48.53
Screenshot 2021-03-29 at 14.48.531592×932 33.4 KB

#5

I ran exactly this code:

Var sSQL As String, dbh As SQLiteDatabase

dbh = new SQLiteDatabase
dbh.Connect ()
dbh.ExecuteSQL ("PRAGMA foreign_keys = on")
dbh.ExecuteSQL ("create table tbl_Countries (fld_ShortName primary key, fld_LongName)")
dbh.ExecuteSQL ("create table tbl_Cities (fld_Country, fld_Zip, fld_City, FOREIGN KEY (fld_Country) references tbl_Countries(fld_ShortName) On Delete Restrict)")
dbh.ExecuteSQL ("insert into tbl_Countries values ('BE', 'Belgium')")
dbh.ExecuteSQL ("insert into tbl_cities values ('BE', 2000, 'Brussels')")
 
sSQL = "DELETE FROM tbl_Countries WHERE  fld_shortName = 'BE'"
Try
  dbh.ExecuteSQL(sSQL)
  MessageBox ("Deleted")
Catch err As DatabaseException
  MessageDialog.Show("Fout " + err.ErrorNumber.ToString)
End Try

.

and got the exception with errornumber 19 and message:

FOREIGN KEY constraint failed.

#6

Thank you, Tim. I changed your code a little bit by adding the primary keys (fld_Country, fld_Zip) to the table tbl_Cities and defining all fields to be Text fields. And yes, with your program I still get the error number 19 (Constraint error)!

But my xojo test program with my testdb (SQLite database file on disk) doesn’t get this constraint error… All database definitions are the same… and I use the xojo code as displayed in my first remark… I also removed #Pragma BreakOnExceptions False (reply by @Javier_Menendez). Strange… I use constraints very frequently and never got this problem…

#7

Mmmmmyes but you didn’t post everything in your OP. I had to go to:

https://www.sqlite.org/foreignkeys.html

to even see how to add a foreign key (I don’t use them).

Are you sure both records such as I inserted are actually in your database? If your DELETE matches no rows and so no rows are removed that is not an error AFAIK.

#9

Yes Tim, the same records are in the database and ‘BE’ can be deleted from tbl_Countries…

I used ‘Foreign keys’ so many times on MySQL, Oracle… This is the first time I use SQLite for a rather small desktop project. 'Foreign keys' work very well: you don’t have to care for inconsistent data (e.g. a country code cannot be deleted when there are cities referring to this country code).

Yes, you can do a check by programming your own SQL statement(s) before deleting any records. But these checks can be done by the DBMS by using Foreign keys. But it seems that SQLite has a problem with foreign keys…

#10

Tim, it seems that xojo doesn’t get an error code from SQLite. The constraint check works well when other tools (e.g. DB Browser for SQLite) are used to add, edit, delete records in the database. Is this a bug that could be posted to the xojo team?

#11

What’s your Xojo version? The internal SQLite Xojo engine changes with time.

#12

What makes you think that? My example (based on your code) does get the exception. You’d need to submit a complete example that fails, for them to pay attention.

#13
Xojo version SQLite version
2020r2 3.33.0
2020r1 3.31.1
2019r3 3.29.0
2019r2 3.28.0
2019r1 3.26.0
2018r4 3.25.3
2018r3 3.24.0
2018r2 3.23.1
2018r1 3.22.0
2017r3 3.20.1
2017r2 3.19.3
#14

Xojo version 2020 Release 2.1

#15

If this is a legacy DB, maybe you could try rebuilding it with the current engine. Who knows you have a legacy bug in its structure?

#16

I just posted my code, statement by statement, into the sqlite3 CLI program. When testing, that is what you should use, as it’s written and maintained by the SQLite team.

Whether I had sqlite3 use an in-memory database, or an on-disk one, I got the same outcome: the constraint error.

#17

Here’s a couple of questions:

  1. Are you setting the PRAGMA each time you connect to the database?

  2. Are you setting it inside a transaction?

Your answers should be:

  1. Yes

  2. No

otherwise it won’t work.

#18

SQLite database version 3.31.0. Can this be a problem?

#19

See my last post.

#20

Don’t think so. But previous operations could lead to inconsistencies.

#21

Try a full dump, and inspect the SQL file. Then you could recreate it using this file.