What is a foreign key?
We can say a foreign key is a link between two or more tables. It is a column or group of columns that link the data between two tables. It acts as a cross-reference between two tables because it references the primary key of another table.
How to create a foreign key in XAMPP table?
To create the foreign key first, we need to create another table to contain the foreign key. Here I have created a second table called products and added four columns as productId(Primary Key, Autoincrement), productName, productPrize, and empId. Here empId will act as a foreign key and it will refer to empId of employee table.
I have added some data to the product table. Here empId refers to the empId of the employee table. So you can assign any product to any employee by adding the employee’s empId in front of any product. The first product is tagged to the employee having empId as 1. Note that we just created the table and column we have not assigned the foreign key to empId column in the products table.
Now, we will assign the empId as foreign key. Go to the Structure tab and click on Relation View.
Here you can define foreign key constraints. In the first text box you can give any name to constraint property like here I have given ForeignKey. Next select RESTRICT for both ON DELETE and ON UPDATE, in the column dropdown select empId this empId is from the products table. Select database as codetej and table as employee since we want to link our foreign key to the employee table. After that lastly, in column select empId, this empId is from employee table. Below this you will get to see the Choose column to display select empId in that, this is a foreign key constraint column. Click on save.
After adding the foreign key constraint if you will go to table data and if you try to hover on each id of empId column. You will get to see the name of the employees associated with that id. As shown below I have hovered on 3 empId and it is showing me, Suresh, as a name. This means we have successfully defined our foreign constraint.
Now, if I try to delete a record from the employees table having any product associate with it then see what happens.
Here I have selected the first record and I am trying to delete it. Click on delete and you will get the below-shown message, check the checkbox Enable foreign key checks, and click on yes.
You will get the error because we have selected ON UPDATE and ON DELETE as RESTRICT and because of that MySQL will check if there is any foreign key constraint associated with that, if yes then it will not delete that record and will give us the following error.
Now, if you don’t want MySQL to check strictly. Go to the Foreign Key constraint which we have defined and select the ON UPDATE and ON DELETE as CASCADE and click on Save.
Now if you will try to delete the first record from employees table it will be deleted without showing any error.
As you can see the record we wanted to delete is not there anymore. Also if you take a look at the products table you will get to see that the product having empId as 1 is not there. It means it got deleted since there is no more reference in the employees table.
So this is what happens when we select ON UPDATE and ON DELETE as RESTRICT and CASCADE. It is not necessary to select both of them as CASCADE or RESTRICT, you can select one as a CASCADE and another as RESTRICT and vice versa. Now, it is your task to find out what will happen if you select ON UPDATE and ON DELETE as SET NULL and NO ACTION.