Foreign Key constraints between tables
The newly announced GA of MySQL Cluster 7.3 (7.3.2) builds upon second DMR (7.3.1 m2) released earlier in the year which added Foreign Keys to MySQL Cluster. Foreign Keys is a feature requested by many, many people and has often been cited as the reason for not being able to replace InnoDB with MySQL Cluster when they needed the extra availability or scalability.
Note that this post is an up-version of the original – and was first published with the 7.3 labs release in June 2012.
What’s a Foreign Key
The majority of readers who are already familiar with Foreign Keys can skip to the next section.
Foreign Keys are a way of implementing relationships/constraints between columns in different tables. For example, in the above figure, we want to make sure that the value of the county column in the towns table has an associated entry in the counties table. In this way, no-one can place a town in a non-existent county and similarly no one can remove a county and leave orphaned towns behind.
We refer to the towns table as the child and the counties table as the parent.
There are different categories of constraints that influence how they’re enforced when a row is updated or deleted from the parent table:
- Cascade: If a row is deleted from the parent then any rows in the child table with a matching FK value will also be deleted. Similarly for changes to the value in the parent table.
- Restrict: A row cannot be deleted from the parent table if this would break a FK constraint with the child table. Similarly for changes to the value in the parent table.
- No Action: Very similar to “Restrict” except that any events/triggers on the parent table will be executed before the constraint is enforced – giving the application writer the option to resolve any FK constraint conflicts using a stored procedure.
- Set NULL: If NULL is a permitted value for the FK column in the child table then it will be set to NULL if the associated data in the parent table is updated or deleted.
- Set Default: If there is a default value for the FK column in the child table then it will be used if the associated data in the parent table is updated or deleted. Note that this is not implemented in this version – the constraint can be added to the schema but any subsequent deletion or update to the column in the parent table will fail.
How to use Foreign Keys with MySQL Cluster
For anyone familiar with using FKs with InnoDB, a lot of this will be very similar – that’s intentional in the design of this Cluster feature. Throughout this section we will use the tables shown in the above figure.
First of all, create the tables and add some data:
mysql> CREATE DATABASE clusterdb;USE clusterdb;
mysql> CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY, country VARCHAR(30))
ENGINE=ndb;
mysql> REPLACE INTO counties VALUES ('Berkshire','England'),('Shropshire','England'),
('Oxfordshire','England'),('Buckinghamshire','England');
mysql> CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county VARCHAR(30),
INDEX county_county_index (county), CONSTRAINT county_town FOREIGN KEY (county)
REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=ndb;
mysql> REPLACE INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire'),
('Shrewsbury','Shropshire'),('Oxford','Oxfordshire');
mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county | country |
+-----------------+---------+
| Shropshire | England |
| Buckinghamshire | England |
| Berkshire | England |
| Oxfordshire | England |
+-----------------+---------+
4 rows in set (0.00 sec)
+------------+-------------+
| town | county |
+------------+-------------+
| Reading | Berkshire |
| Shrewsbury | Shropshire |
| Maidenhead | Berkshire |
| Oxford | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)
Note that the FK is actually defined as part of creating the towns table with CONSTRAINT county_town FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT. county_town is the label assigned to the constraint (if you don’t specify one then one will be automatically created – you can see it by executing SHOW CREATE TABLE towns). The constraint is set up from the county column in the towns (child) table to the county column in the counties (parent) table.
As a first check, we can confirm that values that are not part of the constraint can be modified in the parent table – in this case we’ll move the England/Wales border:
mysql> UPDATE counties SET country='WALES' WHERE county='Shropshire'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Next, to illustrate the kind of user error that can be prevented, we attempt to add a town in a county that has not yet been defined – we want this to fail as it would break the constraint we created:
mysql> INSERT towns VALUES ('Cambridge','Cambridgeshire');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(Unknown error code)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 255 'Foreign key constaint violated: No parent row found' from NDB |
| Error | 1452 | Cannot add or update a child row: a foreign key constraint fails (Unknown error code) |
+---------+------+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
We can also confirm that the constraint checking works in the opposite direction, by attempting to delete a county that has towns defined for it:
mysql> DELETE FROM counties WHERE county='Berkshire'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------+ | Warning | 1296 | Got error 256 'Foreign key constaint violated: Referenced row exists' from NDB | | Error | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) | +---------+------+-------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
It is also possible to delete FK constraints and add new ones, we do that next and at the same time we’ll switch to a CASCADE restriction. Note that adding and deleting FKs are on-line operations. In case you have forgotten the name of the constraint (or we had not explicitly named it) we can use the SHOW CREATE TABLE command to remind us:
mysql> SHOW CREATE TABLE towns; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | towns | CREATE TABLE `towns` ( `town` varchar(30) NOT NULL, `county` varchar(30) DEFAULT NULL, PRIMARY KEY (`town`), KEY `county_county_index` (`county`), CONSTRAINT `county_town` FOREIGN KEY(`county`) REFERENCES `counties` (`county`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE towns DROP FOREIGN KEY county_town; Query OK, 0 rows affected (0.47 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE towns ADD CONSTRAINT county_town FOREIGN KEY (county) REFERENCES counties(county) ON DELETE CASCADE ON UPDATE CASCADE;
The data in the tables will not have changed as part of dropping and adding constraints and so you can always DROP and the re-add constraints if you need to turn FKs off for a while:
mysql> SELECT * FROM counties;SELECT * FROM towns; +-----------------+---------+ | county | country | +-----------------+---------+ | Buckinghamshire | England | | Berkshire | England | | Oxfordshire | England | | Shropshire | WALES | +-----------------+---------+ 4 rows in set (0.00 sec) +------------+-------------+ | town | county | +------------+-------------+ | Reading | Berkshire | | Shrewsbury | Shropshire | | Maidenhead | Berkshire | | Oxford | Oxfordshire | +------------+-------------+ 4 rows in set (0.00 sec)
Now if we delete the county of Berkshire then the CASCADE option will cause all of the associated towns to be deleted too:
mysql> DELETE FROM counties WHERE county='Berkshire'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM counties;SELECT * FROM towns; +-----------------+---------+ | county | country | +-----------------+---------+ | Shropshire | WALES | | Buckinghamshire | England | | Oxfordshire | England | +-----------------+---------+ 3 rows in set (0.00 sec) +------------+-------------+ | town | county | +------------+-------------+ | Shrewsbury | Shropshire | | Oxford | Oxfordshire | +------------+-------------+ 2 rows in set (0.00 sec)
Note that MySQL Cluster implements FKs in the data nodes and the constraints will be enforced regardless of how the data is written (through SQL, javascript, Memcached, ClusterJ, REST or directly through the NDB API). Before illustrating this, we add back the data we have just deleted:
mysql> REPLACE INTO counties VALUES ('Berkshire','England');
Query OK, 1 row affected (0.00 sec)
mysql> REPLACE INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county | country |
+-----------------+---------+
| Shropshire | WALES |
| Buckinghamshire | England |
| Berkshire | England |
| Oxfordshire | England |
+-----------------+---------+
4 rows in set (0.00 sec)
+------------+-------------+
| town | county |
+------------+-------------+
| Reading | Berkshire |
| Shrewsbury | Shropshire |
| Maidenhead | Berkshire |
| Oxford | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)
We’ll then run a simple C++ app to delete the ‘Berkshire’ row from the counties table. The full source for the app is available here but the key part of the code is this:
static void do_change(Ndb &myNdb)
{
const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
const NdbDictionary::Table *countiesTable= myDict->getTable("counties");
char royalBorough[255];
royalBorough[0]=9;
strcpy(royalBorough + 1, "Berkshire");
if (countiesTable == NULL)
APIERROR(myDict->getNdbError());
NdbTransaction *myTransaction= myNdb.startTransaction();
if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
NdbOperation *myOperation= myTransaction->getNdbOperation(countiesTable);
if (myOperation == NULL) APIERROR(myTransaction->getNdbError());
std::cout << "About to delete " << royalBorough << "n";
myOperation->deleteTuple();
myOperation->equal("county", (const char *) royalBorough);
if (myTransaction->execute( NdbTransaction::Commit ) == -1)
APIERROR(myTransaction->getNdbError());
myNdb.closeTransaction(myTransaction);
}
We then run this code and confirm that the cascaded delete has worked:
billy@ws2:~/projects/fk$ ./testfk localhost:1186 About to delete Berkshire mysql> SELECT * FROM counties;SELECT * FROM towns; +-----------------+---------+ | county | country | +-----------------+---------+ | Shropshire | WALES | | Buckinghamshire | England | | Oxfordshire | England | +-----------------+---------+ 3 rows in set (0.00 sec) +------------+-------------+ | town | county | +------------+-------------+ | Shrewsbury | Shropshire | | Oxford | Oxfordshire | +------------+-------------+ 2 rows in set (0.00 sec)
Comparison with InnoDB’s Foreign Keys
As mentioned, we’ve attempted to mirror the way that InnoDB works as much as possible in the way that it implements Foreign Keys as that’s what we think will allow people to move more easily to MySQL Cluster when appropriate. There are a couple of exceptions though:
- InnoDB doesn’t support “No Action” constraints, MySQL Cluster does
- You cannot set up FKs between 2 tables where one is stored using MySQL Cluster and the other InnoDB.
- You cannot change primary keys through the NDB API which means that the MySQL Server actually has to simulate such operations by deleting and re-adding the row. If the PK in the parent table has a FK constraint on it then this causes non-ideal behaviour. With Restrict or No Action constraints, the change will result in an error. With Cascaded constraints, you’d want the rows in the child table to be updated with the new FK value but, the implicit delete of the row from the parent table would remove the associated rows from the child table and the subsequent implicit insert into the parent wouldn’t reinstate the child rows. For this reason, an attempt to add an ON UPDATE CASCADE where the parent column is a primary key will be rejected.
MySQL Workbench
Viewing and updating Foreign Key Constraints in MySQL Workbench
Closing remarks
We believe that this is going to enable a whole new set of applications exploit the advantages of MySQL Cluster (on-line scalability, real-time performance, NoSQL APIs, real-time performance, on-line schema changes,…) either because:
- they want to simplify their application by pushing these referential checks down into the database – letting the developer focus more of their time on extending the application;
- because they’re stuck with 3rd party middleware that is dependent on FKs
- or that the application is already so dependent on FKs that it would be too much pain to remove them.
If you are new to MySQL Cluster then here are some useful resources to get you started: