SQLite Forum

Why does SQLite violate foreign key policy when data is populated through python program?
Login

Why does SQLite violate foreign key policy when data is populated through python program?

(1) By XXX (xxxx89) on 2021-11-30 12:02:17 [source]

I created tables with primary and foreign keys using my SQLite Studio, the foreign keys are enabled already.

I noticed that when I populate my tables from my python application GUI, the foreign keys constraints are not maintained, they are violated and the records get saved successfully. When I run the same program connected to MySQL or MSSQL database tables with the foreign keys too, invalid data which do not match with data of the PK columns are rejected with a notification stating that the foreign key constraint is not met.

I noticed when I decide to populate the tables directly from SQLite studio, the foreign keys constraints are upheld and invalid data which do not exist in the PK columns are rejected.

So why does SQLite accept invalid data populated through Python program?

(2) By Larry Brasfield (larrybr) on 2021-11-30 12:07:52 in reply to 1 [link] [source]

See pragma foreign_keys and note carefully the default state affected by that pragma.

Short answer to "why ...": Because your Python program uses a SQLite library built in a defaulted way and does not enable foreign key enforcement.

(3) By XXX (xxxx89) on 2021-11-30 12:18:38 in reply to 2 [link] [source]

thank you Larry, please have you any clue how I can enforce that in Python?

(4) By Larry Brasfield (larrybr) on 2021-11-30 12:32:04 in reply to 3 [link] [source]

You could issue the pragma I mentioned prior to doing anything for which you want referential integrity enforced.

(5) By XXX (xxxx89) on 2021-11-30 12:44:57 in reply to 2 [link] [source]

Thank you Larry, I have fixed it. It works fine now. I went to my python database connection codes and included conn.execute("PRAGMA foreign_keys = 1") after the connection settings, just before the conn.cursor()