In this post, we will discuss what is Foreign Key in MySQL? How to create a table with Foreign key? and How to add and remove Foreign Key from a table in MySQL?

what is a Foreign Key?

Foreign Key is a column (attribute) or a set of columns that refer to the candidate key of another table.

Example:

Foreign key in MySQL

In the above example ‘programming.roll_no‘ is the foreign key as it is referring to the ‘students.roll_no‘ which is a primary key (i.e candidate key).

Some of the prominent properties of Foreign Key in MySQL are

  • Foriegn Key is a constraint that restricts column to only allow those entries which are present in the column to which it is referring to (i.e primary key of another table).
  • A table in MySQL can have more than one Foreign Key.
  • Foreign Key establish one to many relationship between tables (In the above example one student can learn multiple programming languages).
  • Foreign Key can only refer to candidate keys.

Also, it is important to remember that the table in which the Foreign key is defined is known as the child table and the table to which it is referring to is known as the parent table.

Why Foreign Key can Only Refer to the Candidate key?

Because the candidate key has unique column values. In case, if Foreign Key refers to the column of another table having duplicate values then ambiguity will arise. A foreign key value is referring to which record of the referring table will be ambiguous.

Recommended: Table in MySQL

Create Table with a Foreign Key

The general syntax to create a table with a foreign in MySQL is

Note: Syntax within square brackets [] are optional.

where,

  • IF NOT EXISTS ensures that table would only be created if it doesn’t already exists in the MySQL database.
  • CONSTRAINT constraint_name is used to give name to the Foreign Key constraint. If we exclude it then MySQL automaticaly generates a name for the constraint.
  • ON UPDATE and ON DELETE are subclauses of the FOREIGN KEY clause which defines what referential action should take place in case of an UPDATE or DELETE operation on a key value in the parent table that has matching rows in the child table.
  • referential_action could include CASCADE, SET NULL, RESTRICT, NO OPTION and SET DEFAULT.

If we exclude all the optional syntax then the simplified statement to create a table with a Foreign Key constraint would be

Let’s see some examples for better understanding.

Recommended: Primary key in MySQL

Example 1: Table with single column as a Foreign Key.

In this example, we have created a foreign key named ‘fk_student_roll’ in which the ‘roll_no’ column of the ‘programming’ table is referring to the ‘roll_no’ column of the ‘students’ table.

Hence ‘students’ is the parent table and ‘programming’ is the child table.

Example 2: Table with multiple columns as a Foreign Key.

In this example, we have defined a set of columns as the Foreign Key in which ‘{cust_id, cust_name}’ of ‘orders’ table is referencing the ‘{id, name}’ of the customer’s table.

Notice, the attributes in the ‘customers’ table to which the table ‘orders’ is referring to (i.e {id, name}) has been defined as the primary key.

Because Foreign Key requires referencing columns to be indexed. The primary key constraint in MySQL automatically creates indexes for respective columns of the particular table.

Otherwise, If we try referencing the non-primary key columns during the process of adding a Foreign Key constraint then we will get the following error ‘Failed to add the foreign key constraint. Missing index for constraint‘.

Add a Foreign Key constraint to an Existing Table

The general syntax to add a Foreign Key constraint to an already existing table in MySQL is

where syntax within square brackets is optional and has the same meaning as described before.

If we simplify the statement by excluding the optional syntax, then the statement to add a Foreign Key constraint to an already existing table would be

Let’s see some examples for better understanding.

Example 1: Adding a single attribute Foreign Key constraint.

In this example we first created parent and child table (i.e ‘students’ and ‘programming’ table) then we explicitly added the foreign key constraint using MySQL ALTER TABLE statement.

Example 2: Adding a multiple attribute Foreign Key constraint.

In this example again we had explicitly defined the foreign key constraint in which ‘customers’ is the parent table and ‘orders’ is the child table.

Remove or Drop Foreign Key Constraint

The general syntax to remove or drop foreign key constraint from an existing table in MySQL is

where ‘constraint_name‘ is the name of the foreign key constraint for the table ‘table_name‘.

In case you didn’t specify any name to the constraint but MySQL generated it automatically then use the following syntax to obtain the generated foreign key constraint name in MySQL.

Example:

Hope by now you have got some ideas about the Foreign Key in MySQL.

There are other concepts left out like what would happen ON DELETE or ON UPDATE of records in the parent and child table? Significance of different referential options such as CASCADE, RESTRICT, SET DEFAULT, etc.

We will learn about them in our next post. For now, if you have any doubts or suggestions then comment below.

This Post Has One Comment

Leave a Reply