SQLite Foreign Key problem

Databases
22
  1. My Test program connects to the database, defines the PRAGMA, and (always) deletes the country ‘BE’. Nothing else…
  2. I don’t use transactions for this Test program
23

@TimStreater

This is a dump of this simple database:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "tbl_Cities" (
	"fld_Country"	TEXT,
	"fld_Zip"	TEXT,
	"fld_City"	TEXT,
	FOREIGN KEY("fld_Country") REFERENCES "tbl_Countries"("fld_ShortName") On Delete Restrict,
	PRIMARY KEY("fld_Country","fld_Zip")
);
CREATE TABLE IF NOT EXISTS "tbl_Countries" (
	"fld_ShortName"	TEXT,
	"fld_LongName"	TEXT,
	PRIMARY KEY("fld_ShortName")
);
INSERT INTO "tbl_Cities" VALUES ('BE','2000','Brussels');
INSERT INTO "tbl_Countries" VALUES ('BE','Belgium');
COMMIT;

And this is the code used in xojo test program:

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

I will create a new version of this database based on the dump

24

Why a PK like this? SQLite have some “behaviors” when using compound indexes.

25

Probably you don’t use the correct way to enable the pragma foreign_keys

once connected use: db.ExecuteSQL (“PRAGMA foreign_keys = on”) and you will get the expected result.

26

I use really often compound primary keys in SQLite and never had any problems

27

The combination of fld_Country and fld_Zip creates a unique primary key.

28

Not exactly a problem Antonio, but in many cases we need separated indexes. In the above case, if you search for the field fld_zip, SQLite will do sequential search instead of a indexed one, for example. You will always need both fields or it will not use the index.

29

And as Antonio pointed out, see if the pragma will activate the constraint. :wink:

30

This PRAGMA statement was already in the code immediately after connecting to the database

31

Just out of curiosity, check if something changes if you create an index just for that field in question.

CREATE TABLE IF NOT EXISTS "tbl_Cities" (
	"fld_Country"	TEXT,
	"fld_Zip"	TEXT,
	"fld_City"	TEXT,
	FOREIGN KEY("fld_Country") REFERENCES "tbl_Countries"("fld_ShortName") On Delete Restrict,
	PRIMARY KEY("fld_Country","fld_Zip")
);

CREATE INDEX idx_cities_country ON tbl_Cities(fld_Country);
32

@TimStreater @Antonio_Rinaldi @Javier_Menendez

Splendid news… I dumped the old database and recreated a new database with the dumped SQL sentences. The xojo program connected to the new database now catches the Delete error (error code 19).

Screenshot 2021-03-29 at 21.42.52

One day lost to solve this problem… Thank you all for your help.

1 Like
33

Well, you had a legacy DB structure fail. Good to know.