SQL -Structured Query Language - Sql Key & Constraint - Foreign Key Tutorial

Foreign Key Constraint is used, when we want to make the relationship between two tables. A column with Foreign Key in one table is used to relate Column in another table.

The main purpose of the foreign key is that table2 which has a column with a foreign key, will only able to insert data if the table2 foreign key value is already in the table1 column (column that is linked with foreign key).

It makes the database more secure. Suppose some hacker can insert invalid or not specific data, other than that we want in the database from the frontend, which may create a very big problem for us.

For example, in the case of gender - male or female, but hackers try to enter invalid data (i.e other than male and female). Therefore, it can cause a very big problem in the future.

The syntax for Foreign key-

CREATE TABLE table2
(
    table2_columnname datatype,

    ....[other column with its datatype],

    FOREIGN KEY (table2_columnname ) REFERENCES table1(table1_columnname)
);

Example for Foreign key-

 table 1-

CREATE TABLE  user_salary
(
id int primary key,

user_id int unique,

salary int
);

table 2-

This table user_detail contains the foreign key, which is in a relationship with the user_salary table.

Without the user_salary table, the user_detail table will not be created. So first we have to create a user_salary table.

CREATE TABLE user_detail
(
    user_id int primary key,

    name varchar(50) not null,

    email varchar(50) unique,

    dateofbirth date,

    FOREIGN KEY (user_id) REFERENCES user_salary(user_id)
);

In this, we define user_detail - user_id with foreign key constraint in relationship with user_id of a user_salary table.  With using joins we can retrieve many details from both the table simultaneously.

With constraint name-

CONSTRAINT FK_detailsalary 
FOREIGN KEY (user_id) REFERENCES user_salary(user_id)

It is best practice to add a constraint name for any constraint. just add CONSTRAINT constraint_name before FOREIGN KEY.

By default constraint name is already created. But you can write your own constraint name.

Syntax For Adding Foreign Key With Alter Command-

ALTER TABLE user_detail
ADD FOREIGN KEY (user_id) REFERENCES user_salary(user_id);

With Constraint-

ALTER TABLE user_detail
CONSTRAINT FK_detailsalary 
ADD FOREIGN KEY (user_id) REFERENCES user_salary(user_id);

This syntax will add a foreign key after table creation. If it gives an error make sure that records having a foreign key, should match their value with the record in the reference table.

Syntax For Deleting Foreign Key With Alter Command-

ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_constraint_name;

For example- 

ALTER TABLE user_detail
DROP FOREIGN KEY FK_detailsalary;

This syntax will delete foreign keys with its constraint name.

In Mysql Workbench-

• A table containing a foreign key will not be created without its reference table. First, we have to create a reference table.

• Data with foreign key value will not be inserted in the table if the foreign key value is not present in the reference table. Which makes it more secure.

• Users will not able to delete or update reference table records if their foreign key value is present in any other table.