Mysql automatically updating foreign keys
Errors reported when content is changed are "DML errors" and errors reported when the schema is changed are "DDL errors".So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors.Attempting to do so does not return an error; it simply has no effect.Usually, the parent key of a foreign key constraint is the primary key of the parent table.If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table.The English language error message for foreign key DML errors is usually "foreign key mismatch" but can also be "no such table" if the parent table does not exist.Foreign key DML errors are reported if: CREATE TABLE parent2(a, b, PRIMARY KEY(a,b)); CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); -- Ok CREATE TABLE child9(x REFERENCES parent2); -- Error!
It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode).
If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created.
Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys.
(Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default.
Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.) The application can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled.
CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); -- Error!