![]() ![]() RESTRICT: The change on the parent table is prevented.The allowed actions for ON DELETE and ON UPDATE are: A TRUNCATE TABLE against a table containing one or more foreign keys is executed as a DELETE without WHERE, so that the foreign keys are enforced for each row.Trying to drop a table that is referenced by a foreign key produces a 1217 error ( SQLSTATE '23000').When a value in the column referenced by a foreign key changes and at least one child row exists, MariaDB performs an action which depends on the ON UPDATE clause of the foreign key.When a row in the parent table is deleted and at least one child row exists, MariaDB performs an action which depends on the ON DELETE clause of the foreign key.Trying to insert non-matching rows (or update matching rows in a way that makes them non-matching rows) in the child table produces a 1452 error ( SQLSTATE '23000').MariaDB performs certain checks to guarantee that the data integrity is enforced: However, if at least one of the foreign key values is NULL, the row has no parents, but it is still allowed. A child row matches a parent row if all its foreign key values are identical to a parent row's values in the parent table. Multiple child rows can match the same parent row. If a foreign keys exists, each row in the child table must match a row in the parent table. The parent and the child table must use the same storage engine, and must not be TEMPORARY or partitioned tables. However, the ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL clauses are not allowed in this case. For integer types, the size and sign must also be the same.īoth the foreign key columns and the referenced columns can be PERSISTENT columns. The foreign key columns and the referenced columns must be of the same type, or similar types. ![]() The referenced columns in the parent table must be a an index or a prefix of an index. If MariaDB automatically creates an index for the foreign key (because it does not exist and is not explicitly created), its name will be index_name. Index prefixes are not supported (thus, TEXT and BLOB columns cannot be used as foreign keys). The columns in the child table must be a BTREE (not HASH, RTREE, or FULLTEXT - see SHOW INDEX) index, or the leftmost part of a BTREE index. The symbol clause, if specified, is used in error messages and must be unique in the database. RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT The definition must follow this syntax: ] FOREIGN KEY See the Examples below.įoreign keys are created with CREATE TABLE or ALTER TABLE. However, only the syntax described below creates foreign keys.įrom MariaDB 10.5, MariaDB will attempt to apply the constraint. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's. For example: CREATE TABLE b(for_key INT REFERENCES a(not_key)) Note: Until MariaDB 10.4, MariaDB accepts the shortcut format with a REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |