Overview

MYSQL Foreign Key constraint is always defined as primary key constraint in another table. Foreign key column can only have those values which are mentioned in another table's primary key column.

Foreign key acts as a reference key to the primary key in another table. Foreign key can be a single column or a combination of columns.

Syntax

Syntax for defining Foreign key is

CREATE table table_name (column1 data_type, column2 data type, column1 REFERENCES another_table(primary_column_name));

Example

Let us look closely with the help of example

Consider table hostel_list containing list of hostels in a college

+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(2) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| occupancy | tinyint(1) | YES | | NULL | |
| capacity | int(5) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+

In this table id is a primary key. Let us create another table hostel_list2 in which we will create hid column as foreign key referencing to primary key id of hostel_list table. MYSQL query for foreign key will be 

CREATE TABLE hostel_list2 (hid int(2), name varchar(200), FOREIGN KEY (hid) REFERENCES hostel_list(id));
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| hid | int(2) | YES | MUL | NULL | |
| name | varchar(200) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+



If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.



Trending Articles


Incorrect table definition there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Infix To Postfix Conversion Using Stack in Javascript

How to solve IE/Edge CORS error ?

Web Server vs Application Server Difference Explained

How to setup angular2 in visual studio ide

Binary Search Tree Insert node Implementation in Javascript

How to use ninject dependency injection in mvc

How to Add Comparison Feature in Replace in C-Sharp