MySQL Foreign Keys: A Step by Step Visual Guide

If you want to use SQL, you will need to understand foreign keys.

Are you confused by the different syntax between SQL dialects?

Do you just want a clear breakdown of how to use foreign keys with MySQL?

Let’s get into it.

What is a foreign key in MySQL?

Before we can implement anything, we need to actually understand what we are trying to create.

A foreign key in SQL is a column used to reference a primary key in another table. As a reminder, primary keys are used in SQL to uniquely identify an entry in a table. Foreign keys are used to uniquely reference data contained in another table. Foreign key columns are formally defined using the FOREIGN KEY constraint in MySQL.

The table containing the primary key is called the parent table, while the table containing the foreign key is called the child table. This is need-to-know SQL terminology.

When should you use foreign keys?

There are many instances in which you might want to refer to entries in other tables. Generally, duplicating data within a SQL database is bad practice. Implementing a foreign key helps to alleviate errors when cross-referencing data between tables.

Foreign keys should be used to cross-reference data between tables, eliminate redundancy, and help to minimize errors. Placing a FOREIGN KEY restraint on a column in the Classes table further minimizes errors by preventing any values from being entered that aren’t in the parent table’s primary key column. This helps maintain referential integrity.

Consider a situation in which you want to record which teachers are teaching classes at a university.

Teachers Table

TeacherID (Primary Key)	LastName	FirstName
1	Smith	William
2	Kirk	Charlotte
3	White	Walter

Classes Table

ClassID	ClassName	TeacherID (Foreign Key)
1	Chemistry	3
2	Biology	1
3	Calculus II	2

The TeacherID column in the Classes table refers to the TeacherID column in the Teachers table. In theory, you could just add columns for the name of the teacher into the Classes table. However, duplicating data in this manner can lead to errors. If a teacher legally changes her name, a user will need to manually update it in every location where it is stored. This is a time-intensive process. If a user forgets to update this data in any location, it leads to messy data mismatches across tables.

Best Practices

Foreign keys should always reference another table’s primary key. Although it is technically possible to create a foreign key that references indexes and keys other than the primary key, there is no reason to do so.
Avoid dangling foreign keys. A dangling foreign key is a foreign key that refers to a primary key or column that does not exist. This can happen when a foreign key is declared and the primary key is deleted later.
The data type of the foreign key and the primary key should match. MySQL will throw an error if the data types are completely incompatible, but will sometimes allow similar datatypes. For instance, MySQL may allow a BIGINT foreign key column to reference an INT primary key. Regardless, it’s best practice for the data types to match exactly.
Foreign keys should refer to the entire primary key. In an instance where a primary key in a table consists of multiple columns, the foreign key needs to refer to all of them.

How to declare a table with a foreign key in MySQL

To create a table with a FOREIGN KEY constraint, you denote the foreign key and the primary key that it references.

The following code creates a table containing a foreign key:

CREATE TABLE Teachers (
    TeacherID INT NOT NULL AUTO_INCREMENT,
    TeacherName VARCHAR(20),
    PRIMARY KEY(TeacherID)
);

CREATE TABLE Classes (
	ClassName VARCHAR(20),
	TeacherID int,
	FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);

In this instance, TeacherID is the name of the column containing the foreign key in the Classes table. Teachers(TeacherID) refers to the Teachers table, containing the primary key column TeacherID.

When creating a foreign key, the table being referenced must already exist, and the column being referenced needs to already have a primary key implemented.

Occasionally two tables may need to both contain a foreign key referencing the other table’s primary key. It is not possible to add the foreign key to the first table if the second table does not exist yet. In this case, you will need to create the first table without the foreign key. Next, create the second table with its foreign key referencing the first table. After this, you can alter the first table to add the foreign key referencing the second table.

How to remove a foreign key from a table in MySQL

If you try to drop a column that is part of a foreign key, MySQL will throw an error. You must directly remove the FOREIGN KEY constraint before you can drop the column.

If you did not explicitly define a constraint name, a constraint name was automatically generated. You can find the name of the constraint with the following code:

SHOW CREATE TABLE <TableName>;

You can find the constraint name using the command shown in the image.

This command will give you a list of information for the table, including the names of any foreign key constraints. The foreign key can then be dropped with the following command:

ALTER TABLE <TableName> DROP FOREIGN KEY <FK_Key_Name>;

How to add a foreign key to an existing table MySQL

The following code adds a FOREIGN KEY to an already existing table:

ALTER TABLE <TableName>

ADD FOREIGN KEY (<ColumnName>) REFERENCES <TableName>(<ColumnName>);

There are a few stipulations to consider when adding a foreign key to a table.

The parent table being referenced must already exist.
The column being referenced should already contain a primary key (or any index).
If there is already data contained in child table, each entry in the foreign key column must match a value in the parent table’s primary key column.
The MySQL engine used should be InnoDB

How to show foreign keys for a table in MySQL

To show foreign key constraints within a particular table, you would use the following syntax in MySQL:

SHOW CREATE TABLE ‘<TableName>’;

To display all foreign keys currently defined, we can use the following:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS

Show constraints/foreign keys defined in your database

Common Errors

Foreign Key is not indexed

When trying to create a foreign key, you may get the following error:

Selected column '<ColumnName>' must be indexed and be of a compatible type for a Foreign Key to be created.

This error occurs when the column that you are referencing is not indexed. MySQL requires that a column referenced in a FOREIGN KEY be indexed.

A primary key is one type of index, but you do not necessarily have to create a primary key in order to fix this error. You may create a generic index in MySQL with the following:

CREATE INDEX <index_name> ON <TableName>(<ColumnName>);

There are other types of MySQL indexes that may be used too. For instance, you can declare a UNIQUE column and then create a foreign key referencing that column.

Cannot add or update a foreign key value

When trying to insert or update a row in a table containing a foreign key, you may encounter the following error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

This error occurs when you are trying to insert a value into the foreign key column that does not exist in the referencing table. Consider the example tables from earlier:

Teachers Table

TeacherID	LastName	FirstName
1	Smith	William
2	Kirk	Charlotte
3	White	Walter

Classes Table

ClassID	ClassName	TeacherID
1	Chemistry	3
2	Biology	1
3	Calculus II	2

In these tables, Classes.TeacherID is a foreign key referencing Teachers.TeacherID.

If we try to run the following statement, an error will occur:

INSERT INTO Classes ('ClassName', 'TeacherID') VALUES ('Physics', 4);

This will fail because there is no entry with a TeacherID of 4 in the Teachers table. This error can be avoided by ensuring that all entered foreign keys exist in the parent table’s primary key column.

Referencing column and referenced column are incompatible

When creating a foreign key, the following error may be encountered:

Error Code: 3780. Referencing column '<ColumnName>' and referenced column '<Column2Name>' in foreign key constraint '<ConstraintName>' are incompatible.

This error generally is thrown when the two column’s datatypes are incompatible. For instance, if one column’s datatype is a varchar and the other is an int, this error will be thrown.

If the datatypes for the two columns are the same, then the issue may be that the two columns have different collations or character sets. Old versions of MySQL used latin1_swedish_ci as the default collation. Tables created with a newer version may use a different collation.

In order to fix this error, ensure that both the foreign key column and the primary key column use the same collation for encoding character strings.

You can find information about a table, including the table’s collation by running the following:

SHOW TABLE STATUS where name like 'TABLE_NAME'

Show an individual table’s status in MySQL

Error 150

Error 150 is a generic error in MySQL that occurs when there is a problem creating a foreign key. You may see a message such as the following which references the error number:

Can't create table 'database.table' (errno: 150)

This error is vague and not helpful on its own. However, you can discover a more detailed error message by running the following command immediately afterward:

SHOW ENGINE INNODB STATUS;

After running this command, search the returned text for the heading stating LATEST FOREIGN KEY ERROR. The information provided here will give you a more detailed error message that can be used to troubleshoot further.

Conclusion

Foreign keys are a great tool to link data together between tables. While it is possible to enter the same data in multiple tables, formally enforcing the relationship between tables helps to eliminate errors and maintain referential integrity in your database. Once you understand how foreign keys work, it is easy to link information across tables.

Thanks for reading and look out for more content from RTL Coding. Hope this article was useful and if you would like to support this blog and this content feel free to donate via Paypal to help support more helpful content.