Primary Key & Foreign Key Implementation: MySQL

This video tutorial illustrates the implementation of Primary key and Foreign key, using MySQL.

You can run the same commands / Queries shown in the video on both phpmyadmin as well as on MySQL console.

You can also look at: Primary Foreign Unique Keys, AUTO_INCREMENT: MySQL

Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys have unique values.
Primary Keys are NOT NULL by default.

Creating student table:

1
2
3
4
5
6

CREATE TABLE student(
 stud_id  int AUTO_INCREMENT,
 name     varchar(30) NOT NULL,
 age      int NOT NULL,
 PRIMARY KEY(stud_id)
);

Foreign Key
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Creating enrol table:

1
2
3
4
5
6

CREATE TABLE enrol(
 rol_no  int AUTO_INCREMENT,
 stud_id int NOT NULL,
 PRIMARY KEY(rol_no),
 FOREIGN KEY(stud_id) REFERENCES student(stud_id)
);

Primary Key & Foreign Key Implementation: MySQL

Note:
1. You can have only 1 primary key per table. But can have zero or more foreign keys in a table.
2. You can not delete primary key row directly, without deleting all the rows referring to that row.