Primary Key & Foreign Key Implementation: MySQL

Advertisement:

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

student-enrol-database-tables-structure

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)
);

student-enrol-database-tables

Primary Key & Foreign Key Implementation: MySQL


You need to a flashplayer enabled browser to view this YouTube video

YouTube Link: http://www.youtube.com/watch?v=eTK9TLciRpU [Watch the Video In Full Screen.]



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.

Leave a Reply

Your email address will not be published. Required fields are marked *