The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 10.0.21 and 5.5.45. These are both Stable (GA) releases.

Download MariaDB Galera Cluster 10.0.21

Release Notes Changelog What is MariaDB Galera Cluster?

 

Download MariaDB Galera Cluster 5.5.45

Release Notes Changelog What is MariaDB Galera Cluster?

MariaDB APT and YUM Repository Configuration Generator

See the Release Notes and Changelogs for detailed information on these releases.

Thanks, and enjoy MariaDB!

mariadb-seal-shaded-browntext-alt

Introduction

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example:

As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:

MariaDB foreign key syntax is documented at https://mariadb.com/kb/en/mariadb/foreign-keys/ (and MySQL at http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html). While most of the syntax is parsed and checked when the CREATE TABLE or ALTER TABLE clause is parsed, there are still several error cases that can happen inside InnoDB. Yes, InnoDB has its own internal foreign key constraint parser (in dict0dict.c function dict_create_foreign_constraints_low()).

However, the error messages shown in CREATE or ALTER TABLE, and SHOW WARNINGS in versions of MariaDB prior to 5.5.45 and 10.0.21 are not very informative or clear. There are additional error messages if you issue SHOW ENGINE INNODB STATUS, which help, but were not an ideal solution. In this blog I’ll present a few of the most frequent error cases using MariaDB 5.5.44 and how these error messages are improved in MariaDB 5.5.45 and 10.0.21. I will use the default InnoDB (i.e. XtraDB) but innodb_plugin works very similarly.

Constraint name not unique

Foreign name constraint names must be unique in a database. However, the error message is unclear and leaves a lot unclear:

These messages are not very helpful because there are two foreign key constraints. Looking into SHOW ENGINE INNODB STATUS we get a better message:

In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

No index

There should be an index for columns in a referenced table that contains referenced columns as the first columns.

Fine but again we have no idea which foreign key it was. As before, there is a better message in the SHOW ENGINE INNODB STATUS output:

In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

Referenced table not found

A table that is referenced on foreign key constraint should exist in InnoDB data dictionary. If not:

Both messages are first referring to an internal table name and the foreign key error message is referring to an incorrect name. In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

Temporary tables

Temporary tables can’t have foreign key constraints because temporary tables are not stored to the InnoDB data dictionary.

These error messages do not really help the user, because the actual reason for the error is not printed and the foreign key error references an internal table name. In MariaDB 5.5.45 and 10.0.21 this is clearly improved:

Column count does not match

There should be exactly the same number of columns in both the foreign key column list and the referenced column list. However, this currently raises the following error:

The error message is not clear and the foreign key error refers to an internal table name. In MariaDB 5.5.45 and 10.0.21 there is additional information:

Incorrect cascading

A user may define a foreign key constraint with ON UPDATE SET NULL or ON DELETE SET NULL. However, this requires that the referenced columns are not defined as NOT NULL. Currently, the error message on this situation is:

Both error messages are not very useful, because the first does not really tell how the foreign key constraint is incorrectly formed and later does not say which column has the problem. This is improved in MariaDB 5.5.45 and 10.0.21:

Incorrect types

Column types for foreign key columns and referenced columns should match and use the same character set. If they do not, you currently get:

But do we have an index for the referenced column f1 in the table t2? So if there are multiple columns in both the foreign key column list and the referenced column list, where do we look for the error? In MariaDB 5.5.45 and 10.0.21 this is improved by:

Conclusions

There are several different ways to incorrectly define a foreign key constraint. In many cases when using earlier versions of MariaDB (and MySQL), the error messages produced by these cases were not very clear or helpful. In MariaDB 5.5.45 and 10.0.21 there are clearly improved error messages to help out the user. Naturally, there is always room for further improvements, so feedback is more than welcome!

References

 

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.21 and 5.5.45. These are both Stable (GA) releases.

Download MariaDB 10.0.21

Release Notes Changelog What is MariaDB 10.0?

 

Download MariaDB 5.5.45

Release Notes Changelog What is MariaDB 5.5?

MariaDB APT and YUM Repository Configuration Generator

See the Release Notes and Changelogs for detailed information on these releases.

Thanks, and enjoy MariaDB!

mariadb-seal-shaded-browntext-alt