Why Do SQLite3 Foreign Key Constraints Seem to Fail INSERTs Until `PRAGMA foreign_keys = ON;` is Used?

SQLite3 Foreign Key Constraints: Why INSERTs Succeed Without References

Although SQLite3 provides support for foreign key constraints, an issue arises when attempting to insert records into a child table without adhering to the constraints. This enigmatic behavior can leave you perplexed.

To unravel this mystery, let's examine the SQL script provided. It establishes two tables: "Persons" and "Orders," where "P_Id" in "Orders" is a foreign key referencing the primary key in "Persons."

Upon inserting data into the "Orders" table, even with an empty "Persons" table, the insertion seems to proceed without error. This is because foreign key constraints are not automatically enabled in SQLite3.x.

To rectify this anomaly and enforce foreign key integrity, you must execute the following "PRAGMA" query every time you establish a connection to your SQLite database:

PRAGMA foreign_keys = ON;

By issuing this directive, SQLite3 will diligently enforce all foreign key constraints, preventing insertions that violate those constraints.

This idiosyncrasy stems from SQLite's legacy compatibility with its earlier version, SQLite 2.x. However, in SQLite 4.x, foreign key constraints will become enabled as the default setting, eclipsing this quirky behavior.

The above is the detailed content of Why Do SQLite3 Foreign Key Constraints Seem to Fail INSERTs Until `PRAGMA foreign_keys = ON;` is Used?. For more information, please follow other related articles on the PHP Chinese website!