Difference between Primary Key and Foreign Key

Keys are fundamental elements of the relational database because they establish a relationship between a pair of tables and ensure each record in a table is uniquely identified. Keys have a more particular significance than establishing relationships; they also help with Referential Integrity and they are a major component of table-level integrity. Tables store huge chunks of data in them which usually extend to thousands of records all of which are unsorted and disorganized. Fetching a particular data from those numerous records can be difficult at times or sometimes impossible. This is where Keys come to picture. Here, we will study about the two very important keys of the relational database schema and the difference between them: Primary key and Foreign Key.

What is a Primary Key?

A primary key is a special key which uniquely identifies each record in a table. It is very important in the relational database to have a unique identifier in each row of a table and primary key is just the thing you need to uniquely identify a tuple within a table. A tuple represents a set of value attributes in a relational database. A primary key may refer to a column or a set of columns in a relational database table used to implicitly identify all records in the table. The primary key must be unique for each record as it acts as a unique identifier and it should not contain Null values. Each database must have one and only one primary key.

What is a Foreign Key?

A foreign key refers to a field or a collection of fields in a database record that uniquely identifies a key field of another database record in some other table. In simple terms, it establishes a link between records in two different tables in a database. It can be a column in a table that points to the primary key columns meaning a foreign key defined in a table refers to the primary key of some other table. References are crucial in relational databases to establish links between records which is essential for sorting databases. Foreign keys play an important role in relational database normalization especially when tables need to access other tables.

Difference between Primary Key and Foreign Key

Basics of Primary Key vs Foreign Key

A primary key is a special key in a relational database that acts as a unique identifier for each record meaning it uniquely identifies each row/record in a table and its value should be unique for each row of the table. A foreign key, on the other hand, is a field in one table that link two tables together. It refers to a column or a group of columns that uniquely identifies a row of another table or same table.

Relation of Primary Key vs Foreign Key

A primary key uniquely identifies a record in the relational database table, whereas a foreign key refers to the field in a table which is the primary key of another table. A primary key must be unique and only one primary key is allowed in a table which must be defined, whereas more than one foreign key are allowed in a table.

Duplicate Values of Primary Key vs Foreign Key

A primary key is a combination of UNIQUE and Not Null constraints so no duplicate values can be allowed to have in a primary key field in a relational database table. No two rows are allowed to carry duplicate values for a primary key attribute. Unlike a primary key, foreign key can contain duplicate values and a table in a relational database can contain more than foreign key.

NULL of Primary Key vs Foreign Key

One of the main differences between the two is that unlike primary keys, foreign keys can also contain NULL values. A table in a relational database can have only one primary key which does not allow NULL values.

Temporary Table of Primary Key vs Foreign Key

A primary key constraint can be defined implicitly on temporary tables and their variables, whereas a foreign key constraint cannot be enforced on local or global temporary tables.

Deletion of Primary Key vs Foreign Key

A primary key value cannot be deleted from the parent table which is referred to as a foreign key in the child table. You have to delete the child table first before removing the parent table. On the contrary, a foreign key value can be deleted from the child table even if the value is referred to the primary key of the parent table.

Primary Key vs. Foreign Key: Comparison Chart

Summary of  Primary Key verses Foreign Key

Keys play a crucial role in the existence of database schema to establish links between tables and within a table. Keys establish relationships and enforce different types of integrity, especially table-level and relationship-level integrity. For one, they make sure table contains unique records and the fields you use to establish a relationship between tables must contain matching values. Primary key and foreign key are the two most important and common types of keys used in relational databases. A primary key is a special key used to uniquely identify records in a table, whereas a foreign key is used to establish relationship between two tables. Both are identical in structure but play different roles in relational database schema.

Sharing is caring!