Difference Between Primary key and Foreign key in DBMS

Keys are the crucial part of DBMS they are used to identify and establish a relation between the tables in a schema. Now, today we are going to discuss two very important keys of DBMS i.e. Primary key and Foreign key, and we will also discuss the difference between primary key and foreign key.

On the way let me tell you the basic difference between primary and foreign key which is primary key is one of the chosen candidate keys by database designer whereas, a foreign key is a key that refers to the primary key of another relation.

There many other differences between these two, let us identify those differences with the help of comparison chart shown below.

Content: Primary  Vs  Candidate key

1. Comparison Chart
2. Definition
3. Key Differences
4. Conclusion

Comparison Chart

Basis for Comparison	Primary Key	Foreign Key
Basic	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.
NULL	Primary key value can never be NULL.	Foreign key accepts NULL value.
Duplicate	No two tuples in a relation carry duplicate values for a primary key attribute.	Tuples can carry duplicate value for a foreign key attribute.
Range	There can be only one primary key of a relation.	There can be multiple foreign keys in a relation.
Temporary Table	Primary key constraint can be defined on the temporary tables.	Foreign Key constraint can not be defined on the temporary tables.
Clustered index	By default, a primary key is clustered indexed.	Foreign key is not clustered indexed automatically; it has to be done manually.
Insertion	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.
Deletion	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.

Definition of Primary Key

A primary key uniquely defines tuples in a relation. It can be a single attribute in a relation, or it can be a set of attributes in a relation. The value of the primary key attribute should never or rarely changed. Because it is a principal, mean to identify any record in a database. Change in any attribute value of primary key would create confusion.

Database designer chooses one of the candidate keys as a primary key,  taking some points into consideration. The first consideration is a primary key attribute value can never contain NULL value. Because, if a primary key attribute value contains NULL, it means we can not identify that record in the table.

It also violates the entity integrity constraint. Second consideration is, no two tuples in a table can contain the same value for a primary key attribute, as it would violate uniqueness among the tuples.

There can only be one primary key for any relation. The primary key is by default cluster-indexed, which means all tuples in a table are sorted, based on the primary keys attributes values. The primary key constraint can be defined on a temporary table. Intermediary tables created during the execution of a query are called temporary tables.

While deleting a tuple from a relation, one must take care that the deleted tuple’s primary key value, is not still present in the foreign key column of referencing relation. Whereas the insertion does not have any constraints on a primary key.

The primary key of a table when used in some other table then it becomes foreign key for that table. Foreign key constraints are discussed below.

Definition of Foreign Key

When a relation R1, among its attributes, has a primary key of other relation R2, then that attribute is called Foreign key for relation R1. The relation R1 containing the foreign key is called referencing relation as it refers primary key of relation R2 and relation R2 is called referenced relation.

Unlike the primary key, the foreign key can accept NULL values because, it do not have the task of identifying a record distinctly in a relation, as we have the primary key for this. In the same way, the foreign key also accepts duplicate values.

A relation can have multiple foreign keys, as it can have different attributes that are primary keys in different relations. Foreign key constraint can not be defined on the temporary tables, nor a foreign key is a cluster-indexed attribute.

While inserting a value into a foreign key column of referencing relation, make it sure that the inserting value must be present in the primary key column of referenced relation. Whereas, there is no constraint while deleting a value from the foreign key column.

Conclusion

Both, primary key and foreign key are essential for a schema. A primary key defines each tuple in a relation uniquely whereas, a foreign key is used to create a link between two relations.