Difference between Primary Key and Foreign Key

In computer programming, a key is a single or combination of multiple fields in a table. It is used to fetch or retrieve records/data-rows from data table according to the condition/requirement.

Keys are also used to create a relationship among different database tables or views. In DBMS (Database Management system), there are two important keys - Primary key and Foreign key. These keys seem identical, but actually, both are different in features and behaviors.

Primary Keys

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 the primary key would create confusion. The primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.

The primary key can also be specified directly during table creation. In the SQL Standard, primary keys may consist of one or multiple columns. Each column participating in the primary key is implicitly defined as NOT NULL.

Foreign Keys

A foreign key is a set of one or more columns in a table that refers to the primary key in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. In database relational modeling and implementation, a unique key is a set of zero or more attributes, the values of which are guaranteed to be unique for each tuple (row) in a relation. The value or combination of values of unique key attributes for any tuple cannot be duplicated for any other tuples in that relation. Foreign keys play an important role in relational database normalization especially when tables need to access other tables.

Differences between Primary key and Foreign key

• A primary key is a special key in a relational database that acts as a unique identifier for each record.

• A foreign key, on the other hand, is a field in one table that links two tables together.

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

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

• A table can have only one primary key but you can have more than one foreign key.

• Primary key doesn’t accept null value but you can add null value in the foreign key.

• Primary key doesn’t accept duplicate value but you can have duplicate values in the foreign key.

• When you create primary key clustered index created automatically but in the foreign key, no index created automatically. But you can create a non-clustered index manually.

• The primary key is used to uniquely identify the rows but foreign key refers primary in of other tables.

• The Primary key attribute contains unique values.

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

• The foreign key constraint cannot be applied to the temporary tables.

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

• The foreign key is called as a referential entity so that while inserting the value into the foreign key table make sure that the value is present into the 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 a referenced primary key column of referenced relation.

Stock photo from Blackboard