So I ran across a situation today dealing with having to update a field but the user was unable to do so because of the related foreign key constraints.

This blog post with be a simple example showing a foreign key and how to update them if you have to do so.

First let us create a simple table and populate it with random data.

CREATE TABLE `table_w_code` (
`SOMECode` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`NameofCode` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`SOMECode`)
) ENGINE=InnoDB ;


Now we will need another table that has a foreign key tied to our previous table.

[anothermysqldba]> CREATE TABLE `table_with_fk` (
`SOMEID` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`SOMECode` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`Somemorefields` varchar(150) COLLATE utf8_unicode_ci …

The other day I was running pt-duplicate-key-checker on behalf of a customer and noticed some peculiar recommendations on an InnoDB table with an odd structure (no PRIMARY key, but multiple UNIQUE constraints). This got me thinking about how InnoDB promotes UNIQUE constraints to the role of PRIMARY KEYs. The documentation is pretty clear:

[DOCS]
When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key …