MySQL Logo

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.

mysql> create table users (
    id int(11) not null primary key auto_increment,
    email varchar(255) not null
);
Query OK, 0 rows affected (0.01 sec)
mysql> create table user_logins (
    id int(11) not null primary key auto_increment,
    login_time datetime not null,
    user_id int(50) unsigned not null
);
Query OK, 0 rows affected (0.01 sec)

Now when we try to add the foreign key constraint we’ll get an error.

mysql> alter table user_logins add foreign key (user_id) references users (id);
ERROR 1215 (HY000): Cannot add foreign key constraint

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.

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------+
| Level   | Code | Message                                                                 |
+---------+------+-------------------------------------------------------------------------+
| Warning |  150 | 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.                                            |
| Error   | 1215 | Cannot add foreign key constraint                                       |
+---------+------+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

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 users.id is int(11) but user_logins.user_id is 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.

mysql> drop table user_logins;
Query OK, 0 rows affected (0.00 sec)

Now we can recreate it.

mysql> create table user_logins (
    id int(11) not null primary key auto_increment,
    login_time datetime not null,
    user_id int(11) not null
);
Query OK, 0 rows affected (0.01 sec)

Now we can successfully create the foreign key constraint.

mysql> alter table user_logins add foreign key (user_id) references users (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Conclusion

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.