How to Add Foreign Key in SQL?
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)
We have created two primary keys “Emp_ID” to an “employee” table, “Dept_ID” to “department” table and “Emp_ID” as a foreign key to “department” table while creating tables. To verify whether the keys are added to tables or not, we have used DESC command.
- Foreign key creation with constraint name
A foreign key can be created even at the time of creating tables along with the constraint name. This constraint name will be useful while dropping a foreign key from a table without dropping an entire table.
Syntax:
CREATE TABLE tablename(ColumnName1 Datatype(SIZE) PRIMARY KEY, ColumnNameN Datatype(SIZE), CONSTRAINT ConstraintName FOREIGN KEY( ColumnName ) REFERENCES PARENT_TABLE_NAME(Primary_Key_ColumnName));
Example:
We will create a primary key and foreign key while creating the “employee” and “department” tables respectively. Foreign key will be created along with the foreign key constraint while creating the table itself.
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.11 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.01 sec) mysql> CREATE TABLE department(Dept_ID INT NOT NULL PRIMARY KEY, Dept_Name VARCHAR(40), Emp_ID INT NOT NULL, CONSTRAINT emp_id_fk FOREIGN KEY(Emp_ID) REFERENCES employee(Emp_ID)); Query OK, 0 rows affected (0.25 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.04 sec)
We have created two primary keys “Emp_ID” to an “employee” table, “Dept_ID” to “department” table and “Emp_ID” as a foreign key to “department” table while creating tables. Here, we have also added foreign key constraint named as “emp_id_fk”. To verify whether the keys are added to tables or not, we have used DESC command.
- Foreign key creation using ALTER command
It is possible to create a foreign key even after table creation. While creating a table if we have not added a foreign key to it and after that we need to add the foreign key to an existing table then we will use ALTER command in that case.
Syntax:
ALTER TABLE Parent_TableName ADD FOREIGN KEY(ColumnName) REFERENCES Child_TableName(ColumnName);
Example:
We will add a foreign key to an existing table using ALTER command.
mysql> USE employeedb; Database changed mysql> CREATE TABLE employee(Emp_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Emp_Name VARCHAR(40), Emp_Salary VARCHAR(40)); Query OK, 0 rows affected (0.16 sec) mysql> DESC employee; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | Emp_ID | int(11) | NO | PRI | NULL | auto_increment | | Emp_Name | varchar(40) | YES | | NULL | | | Emp_Salary | varchar(40) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> CREATE TABLE department(Dept_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Dept_Name VARCHAR(40), Emp_ID INT NOT NULL); Query OK, 0 rows affected (0.12 sec) mysql> ALTER TABLE department ADD FOREIGN KEY(Emp_ID) REFERENCES employee(Emp_ID); Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC department; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | Dept_ID | int(11) | NO | PRI | NULL | auto_increment | | Dept_Name | varchar(40) | YES | | NULL | | | Emp_ID | int(11) | NO | MUL | NULL | | +-----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)
We have created two primary keys “Emp_ID” to an “employee” table and “Dept_ID” to “department” table while creating tables. Then using ALTER command we have added “Emp_ID” as foreign key to department table. To verify whether the keys are added to tables or not, we have used DESC command.