Contents
How is a foreign key composed in MariaDB?
It is composed by a column (or a set of columns) in a table called the child table, which references to a column (or a set of columns) in a table called the parent table. If foreign keys are used, MariaDB performs some checks to enforce that some integrity rules are always enforced.
Can a column be an index in MariaDB?
The columns in the child table must be an index, or the leftmost part of an index. Index prefixes are not supported (thus, TEXT and BLOB columns cannot be used as foreign keys). 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.
When to use on update clause in MariaDB?
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. 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.
What does no action in MariaDB stand for?
NO ACTION: Synonym for RESTRICT . CASCADE: The change is allowed and propagates on the child table. For example, if a parent row is deleted, the child row is also deleted; if a parent row’s ID changes, the child row’s ID will also change.
How to add a FOREIGN KEY constraint to an existing table?
To add a foreign key constraint to an existing table, you use the alter table statement: foreign key [fk_name] (column_list) references parent_table (column_list) [on delete reference_option] [on update reference_option] To drop a foreign constraint, you use the alter table drop constraint statement:
Can a partitioned table contain a foreign key?
Partitioned tables cannot contain foreign keys, and cannot be referenced by a foreign key. Note: MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS’s.
What happens if you drop a table with a foreign key?
Trying to drop a table that is referenced by a foreign key produces a 1217 error ( SQLSTATE ‘23000’). 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.