In this article we will see "How to create/drop FOREIGN KEY Constraint in MySql" and "FOREIGN KEY Constraint query syntax with example".

What is FOREIGN KEY in MySql

Foreign key is used to link two tables together in mySql, primary key of one table is used as a foreign key in another table. Lets assume we have following already created table EMPLOYEE.



/*Parent table : EMPLOYEE */
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| email | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+



Now we can use primary key column of this table "id", as a foreign key of another table.

Create FOREIGN KEY in MySql example

"FOREIGN KEY" can be used to create a foreign key in mySql while creating a table, as shown in the example below.


CREATE TABLE DEPARTMENT (
    id int,
    name varchar(30),
    EMPLOYEE_ID int,
    PRIMARY KEY (id),
    CONSTRAINT FK_Emp_Dept FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(id)
);



/*Child table : DEPARTMENT */
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | NO   | PRI | 0       |       |
| name        | varchar(30) | YES  |     | NULL    |       |
| EMPLOYEE_ID | int(11)     | YES  | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+


Drop FOREIGN KEY in MySql example

We can drop foreign key from a table Using ALTER TABLE and Foreign Key name, as shown in the example below.


ALTER TABLE DEPARTMENT
DROP FOREIGN KEY FK_Emp_Dept;


In this article we have seen "How to create/drop FOREIGN KEY Constraint in MySql" and "FOREIGN KEY Constraint query syntax with example", in upcoming articles we will see more about Mysql syntax and queries with example.
  • By Techburps.com
  • Oct 20, 2017
  • MySql