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
Difference between Primary Key and Foreign Key
|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.|
Create Primary Key using CREATE TABLE
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
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
We can also add foreign key after the table is created.
ALTER TABLE Marks ADD FOREIGN KEY (StudentID) REFERENCES Students(ID);