Mysql automatically updating foreign keys

The My SQL database requires Inno DB to support transactions, foreign keys and referential integrity.

This tutorial shows how to configure the My SQL database for the Inno DB database engine and examples showing the use of transactions and foreign keys.

Foreign Keys is a feature requested by many, many people and has often been cited as the reason for not being able to replace Inno DB with My SQL 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.

More than one UNIQUE column can be used in a table.

A FOREIGN KEY in My SQL creates a link between two tables by one specific column of both tables.

Foreign keys can only be used with storage engines that support them.

The purpose of inducing constraints is to enforce the integrity of a database.

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.

First of all, create the tables and add some data: 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 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) 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: 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) 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) 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 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) 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 My SQL Cluster implements FKs in the data nodes and the constraints will be enforced regardless of how the data is written (through SQL, javascript, Memcached, Cluster J, REST or directly through the NDB API).

Before illustrating this, we add back the data we have just deleted: 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) 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) As mentioned, we’ve attempted to mirror the way that Inno DB 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 My SQL Cluster when appropriate.