
        
        
            
                
                
                    ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES parent_table (parent_column);

Where:

table_name is the name of the existing table we would like to add a foreign key to.
column_name is the name of the column we would like to make the foreign key.
parent_table is the name of the table within which the column we reference is contained.
parent_column is the name of the column to link to using the foreign key.

Example

Consider the following two tables:

pupil table:

id	name
1	axel
2	bobby
3	cathy

Refer here for code to generate the pupil table.

product table:

id	name	bought_by
1	computer	1
2	hair dryer	3

Refer here for code to generate the product table.

To add a foreign key constraint to bought_by column of product table to refer to id column of pupil table:


        
        
            
                
                
                    ALTER TABLE product ADD FOREIGN KEY (bought_by) REFERENCES pupil (id);
                
            
            Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

Now we can make sure that any value we have for bought_by has a corresponding matching value in the id column of pupil. This means we will always have information on the name of the pupil who bought a particular product.

MySQL | Foreign keys

A foreign key in MySQL refers to a column (or a group of columns) in one table that refers to the primary key in another table. The table containing the foreign key is referred to as the child table, while the table containing the primary key being referred to is called the parent table.

chevron_right