Difference Between Primary Key and Foreign Key :

In my previous article i have already explained about the primary key with real life example as well as foreign key with real life example.I got so many comments on the article where people need article on Difference Between Primary Key and Foreign Key.I will give here the difference between primary key and foreign key with real life examples in this article.The Real life examples are very important in this article. These example gives you clarity about the difference between primary key and foreign key.

The Key difference between primary key and foreign key is the primary key is column which has n number of unique values and foreign key is key which behaves like primary key in parent table and a foreign key in child table.Primary key identify uniquely every row which can not be null. it can not be a duplicate.Foreign key create relationship between two tables which can be null. Foreign key can be a duplicate.The basic topic in this article is Difference between Primary Key and Foreign Key.In this article i will first explain you the difference between primary key and foreign key in multiple bullet-points with real examples.Then I will explain the difference in to tabular format which gives the correct idea to the users.

Difference Between Primary Key and Foreign Key bullet-points :

I would like to explain the difference between primary key and foreign key in different bullet points.This section provides you the difference in points.

1.Definition of Primary Key and Foreign Key :

Primary Key :

A primary is a set of attributes or you can call it as a candidate key that distinctly identifies a record in a relation.

Foreign Key :

A foreign key in a table refers to the primary key of another table.

2.Null Values :

Primary Key :

Primary key attribute does not contain the null values.

Foreign Key :

Foreign key attribute contains the null value.

3.Uniqueness :

Primary Key :

The Primary key attribute contains unique values.

Foreign Key :

The Foreign key attribute does contain the duplicate values.

4.Indexing :

Primary Key : 

By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.

Foreign Key :

Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.

5.Temporary Table :

Primary Key :

The primary key constraint can be applied to the temporary tables.

Foreign Key :

Foreign key constraint can not be applied to the temporary tables.

6.Data Insertion :

Primary Key :

There is no restriction of inserting the values into the column with primary key table.

Foreign key :

The foreign key is called as referential entity so that while inserting the value in to the foreign key table make sure that the value is present in to primary key column.

7.Data Deletion :

Primary Key :

While deleting a value from primary key column make sure that the deleted attribute value is not present in the referencing foreign key column.

Foreign Key :

There is no restriction in deleting the column values in foreign key column.

8.Count :

Primary Key :

We can have only one primary key in the table.

If you want to check the example of primary key kindly click here.

Foreign Key :

We can have more than one foreign keys in the table.

If you want to check the example of foreign key kindly click here.

9.Real Life Example:

Primary Key :

The following example where the PK_Roll_Number is primary key.

CREATE TABLE Student

(

RollNo Number (10),

FName Varchar2 (15),

LName Varchar2 (15),

Location Varchar2 (10),

CONSTRAINT PK_Roll_Number PRIMARY KEY (Roll_no)

);

Foreign Key :

The Following query gives you the foreign key for Student3FK  which indicates with REFERENCES Clause:

CREATE TABLE Student3FK

(

Class Number (5),

RollNo Number (10) REFERENCES Student (RollNo),

Subject Varchar2 (15),

Fees Number (10, 2)

);

These are some important bullet-points of difference between Primary key and foreign key with real life example. In next section i will share the difference between primary key and foreign key in tabular format.

Difference Between Primary Key and Foreign Key in tabular format :

 

Primary Key	Foreign Key
Primary Key is a chosen candidate key that uniquely defines a tuple in a relation.	Foreign key in a table refers to the primary key of other table.
Primary key value can never be NULL.	Foreign key accepts NULL value.
No two tuples in a relation carry duplicate values for a primary key attribute.	Tuples can carry duplicate value for a foreign key attribute.
There can be only one primary key of a relation.	There can be multiple foreign keys in a relation.
Primary key constraint can be defined on the temporary tables.	Foreign Key constraint can not be defined on the temporary tables.
By default, a primary key is clustered indexed.	Foreign key is not clustered indexed automatically; it has to be done manually.
We can insert a value to a primary key attribute, even if the referencing foreign key does not have that value in its column.	We can not insert a value to a foreign key, if that value is not present in the referenced primary key column.
Before you delete a primary key value, make sure that value is not still present in the referencing foreign key column of referencing table.	You can delete a value from foreign key column without bothering, whether that value is present in referenced primary key column of referenced relation.

 

In above section i have explained the difference between Primary Key and Foreign Key Constraint in tabular format.Hope you like this article on Difference Between Primary Key and Foreign Key constraint. If you like this article or if you have any suggestions with the same kindly comment in comments section.