04
Using consistent data types for columns
I came across this error recently when trying to modify the data type of a column.
ERROR 1025 (HY000): Error on rename of './sakila/#sql-1d91_5' to './sakila/inventory' (errno: 150)
Not the first time, and not the last time. A common problem with InnoDB tables, is the lack of information, you need to dig deeper with the following command (and appropriate security a well organized security profile will NOT have).
mysql> SHOW ENGINE INNODB STATUS;
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
080717 20:00:28 Error in foreign key constraint of table sakila/inventory:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
CONSTRAINT "fk_inventory_film" FOREIGN KEY ("film_id") REFERENCES "film" ("film_id") ON UPDATE CASCADE
...
You also need to dig though the output of the command to find this, on a larger system this can be quite a lot of information just to find the details of the error. (It would be nice if there was an easier way.)
The result of this error is the columns in a foreign key relationship need to be of the same data type. This is actually a good thing, and MySQL generally operates much better in joins when the join columns are consistent.
On the assumption that you use surrogate primary keys for tables, this is a candidate for a naming standard for primary keys should be the primary key name is unique column name within your schema.
For example, if you call all your primary key’s ‘id’, your foreign key’s are normally ‘table_id’. While this is a common approach that I promoted myself for many years, it’s easy to read and consistent, actually naming every primary key uniquely provides two great benefits.
First, you can easily identify relationships in your entire schema without even knowing about the schema in detail. Second, you can leverage the benefit of the INFORMATION_SCHEMA to in the case of this post, confirm the data types are consistent for all matching columns, even when Referential Integrity is not used.
So, instead of using ‘id’, use ‘actor_id’, ‘film_id’, ‘user_id’ etc. For any self join keys, I normally prefix with parent, so ‘parent_user_id’ for example if you have a hierarchy within a table.
