Difference Between Primary Key And Foreign Key

Primary key and foreign key work on different concepts of MySQL table. Before starting comparing let’s understand what is the primary key and foreign key in MySQL table.

What is a Primary Key?

A primary key is a single field which uniquely identifies each record in the table.

Note: Every Primary key is a Unique key, but every Unique key is not a Primary key.

What is Foreign Key?

A foreign key is a field in the table which is referencing the primary key in another table. The foreign key provides a relation between tew tables. A foreign key is also known as a reference key.

Primary key and foreign key both look similar but both are very different by their uses and behaviors. Here I have pointed out some major differences between Primary Key and Foreign Key.

Also read: Unique Key in MySQL

Index

Difference Between Primary Key and Foreign Key

The major difference between the primary key and foreign key is that the primary key represents the unique index of table, besides the foreign key represents the reference of a column with the primary key of a table.

Primary Key	Foreign Key
A primary key uniquely identifies every record in the table.	A foreign key is a column in the table which is the primary key in another table.
A primary key can’t be NULL.	A foreign key can be NULL.
A primary key is by default an index.	A foreign key is not by default an index but we can create on it.
There is always one primary key in a table.	Multiple Foreign keys are allowed in a single table.

How to Create Primary Key and Foreign key

Create Primary Key Using CREATE TABLE SQL Query

First, create a Table Students with the following Query which creates the primary key on ID field.

CREATE TABLE Students (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Create Foreign Key Using CREATE TABLE SQL Query

Now, let’s create a child table Marks which has a relation with Students table.

Following query creates a foreign key on StudentID field in Marks table which references to ID field in Students table.

CREATE TABLE Marks (
    ID int NOT NULL,
    StudentID int NOT NULL,
    Science int NOT NULL,
    Maths int NOT NULL,
    Chemistry int NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (StudentID) REFERENCES Students(ID)
);

Create FOREIGN KEY Using ALTER TABLE SQL Query

We can also add foreign key after the table is created.

ALTER TABLE Marks
ADD FOREIGN KEY (StudentID) REFERENCES Students(ID);

Conclusion

I hope now you have a complete understanding of the difference between the primary key and the foreign key.

Enjoy Coding 🙂