Foreign key constraints in SQL provide an excellent way to make sure that our databases don’t have orphaned records or invalid relationships. They can be a bit finicky if we’re not paying attention when we create them. This article will discuss how to fix one of the more opaque errors when we’re creating foreign key constraints.
For our example, we’re going to create a
users table to track our users and a
user_logins table to log every time they log in.
Now when we try to add the foreign key constraint we’ll get an error.
This is one of those cases where MySQL provides a less than helpful error message. So to get the information we need to debug this further we’re going to use the
show warnings command to get the extended information.
Again we see the 1215 error but we also get a 150 error but with another cryptic error message.
Create table ‘database/#sql-4c1_17’ with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
When we get this error message we need to verify that the column definitions on both tables are identical. If they’re not identical MySQL will not allow the foreign key constraint to be created. Notice in our example
int(50) unsigned. This is what is causing the foreign key creation to fail.
As a fun aside, let’s look at the “Create table” part of the error message. Why is MySQL telling us that it can’t create a table when we’re asking it to alter a table? When we ask MySQL to alter a table it can’t easily just change that piece of data so it creates a temporary table (in this case #sql-4c1_17), copies the data from the original table to the new table, deletes the original table, and then renames the temp table to the original.
To fix this, we have two options. We can
change one of the columns to match the other or we can
drop one of the tables and recreate it. Because our
user_logins table is empty we’ll drop that.
Now we can recreate it.
Now we can successfully create the foreign key constraint.
Foreign key constraints provide an excellent way to prevent orphaned and invalid data in our database. It is a must for all columns that reference another table and should be a requirement.
Scott is the Director of Technology at WeCare Connect where he strives to provide solutions for his customers needs. He's the father of two and can be found most weekends working on projects around the house with his loving partner.