How to Add Foreign Key in SQL?

Foreign key is an attribute or a set of attributes that references to primary key of same table or another table (relation).

  • Foreign key creation along with table creation

A foreign key can be created even at the time of creating tables.

Syntax:

CREATE TABLE tablename(ColumnName1 Datatype(SIZE) PRIMARY KEY, ColumnNameN Datatype(SIZE), FOREIGN KEY( ColumnName ) REFERENCES PARENT_TABLE_NAME(Primary_Key_ColumnName));

Example:

Firstly, we will create a database with name “employeedb”. Then in that database we will create two tables “employee” and “department”. We will consider these tables and database for all the subsequent examples.

We will create a primary key and foreign key while creating the “employee” and “department” tables respectively.

mysql> USE employeedb;

Database changed

mysql> CREATE TABLE employee(Emp_ID INT NOT NULL PRIMARY KEY, Emp_Name VARCHAR(40), Emp_Salary VARCHAR(40));

Query OK, 0 rows affected (0.07 sec)

mysql> DESC employee;

+ — — — — — — + — — — — — — -+ — — — + — — -+ — — — — -+ — — — -+

| Field | Type | Null | Key | Default | Extra |

+ — — — — — — + — — — — — — -+ — — — + — — -+ — — — — -+ — — — -+

| Emp_ID | int(11) | NO | PRI | NULL | |

| Emp_Name | varchar(40) | YES | | NULL | |

| Emp_Salary | varchar(40) | YES | | NULL | |

+ — — — — — — + — — — — — — -+ — — — + — — -+ — — — — -+ — — — -+

3 rows in set (0.09 sec)

mysql> CREATE TABLE department(Dept_ID INT NOT NULL PRIMARY KEY, Dept_Name VARCHAR(40), Emp_ID INT NOT NULL, FOREIGN KEY(Emp_ID) REFERENCES employee(Emp_ID));

Query OK, 0 rows affected (0.20 sec)

mysql> DESC department;

+ — — — — — -+ — — — — — — -+ — — — + — — -+ — — — — -+ — — — -+

| Field | Type | Null | Key | Default | Extra |

+ — — — — — -+ — — — — — — -+ — — — + — — -+ — — — — -+ — — — -+

| Dept_ID | int(11) | NO | PRI | NULL | |

| Dept_Name | varchar(40) | YES | | NULL | |

| Emp_ID | int(11) | NO | MUL | NULL | |

+ — — — — — -+ — — — — — — -+ — — — + — — -+ — — — — -+ — — — -+

3 rows in set (0.02 sec)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store