Difference Between Primary Key and Foreign Key

Difference between Primary Key and Foreign Key

The major difference between primary key and foreign key is that the primary key is used to identify each entry in the table, whereas the foreign key is used to connect two tables.

Primary Key vs Foreign Key

Primary Key	Foreign Key
A relation can have only one primary key.	A relation can have more than one foreign key.
A primary key value cannot be null.	A foreign key allows null values.
A primary key is a combination of unique and not null constraints.	A foreign key can contain duplicate values.
A primary key value cannot be deleted from the parent table.	A foreign key can be deleted from the child’s table.
Its constraints can be implicitly defined on the temporary tables.	Its constraint cannot be defined on the local and global temporary tables.
It uniquely identifies a record in the relational database table.	It refers to the field in a table that is the primary key of the same or another table.

What is a Key?

A key in DBMS or RDBMS is a constraint that ensures the uniqueness of data in the relation or table. RDBMS consists of various keys like candidate key, primary key, super key and foreign key. For a relation in RDBMS, it is necessary to have at least one candidate or primary key.

SQL Server has two types of keys: main and foreign. While they appear to be the same, they differ in functionality and behaviors.

Difference between Primary Key and Foreign Key with Example

Let us now understand the difference between foreign key and Primary Key with an example. Check out the tables provided in the image shown below:

In table 1(STUDENT), the key STUD_NO is the primary key, whereas the STUD_NAME, STUD_COUNT, and STUD_AG are candidate keys. Now, STUD_NO in table 2(STUDENT_COURSE), STUD_NO will be the foreign key for table 1 relation(STUDENT).

What is a Primary Key?

A primary key is one of the candidate keys. Now after understanding the difference between the primary key and foreign key, we have to understand the candidate key and super key. A candidate key is the minimal set of attributes that can uniquely identify a row in a relation. The uniqueness of the table records is usually the focus of a primary key. And each row in the database is uniquely identified by a column or a collection of columns. It signifies that there should be no duplicate values for the same column across the table.

A candidate key is a subset of a super key. A super key can identify a relation uniquely and may not be the minimal set. Super key acts as a superset for the candidate key. All the candidate keys are also super keys, but vice versa is not true. A primary key is one of the candidate keys chosen by the administrator to identify row/ tuples uniquely.

What is a Foreign Key?

A foreign key refers to one or more relations in RDBMS. One of the major concerns of having a foreign key is data integrity between two different relations. A foreign key is the primary key of the same or a different relational instance.

The foreign key is usually used to establish a link or relationship between the two tables. The foreign key’s principal purpose is to maintain data integrity between two independent instances of an entity simultaneously.

Importance of Primary and Foreign Keys in Database Design

The primary and foreign keys are essential elements of database design, and their importance cannot be overstated. They ensure data integrity, improve query performance, allow for efficient data relationships, and facilitate database maintenance.

Here’s a tabular and precise description of the importance of primary and foreign keys in database design:

Importance	Description
Data Integrity	Primary keys ensure that each record in a table is unique, while foreign keys maintain referential integrity between related tables. This helps ensure that the data in the database is accurate and consistent.
Query Performance	Primary and foreign keys are used to create indexes, which can speed up query performance. Indexes allow the database to quickly find and retrieve the data needed to satisfy a query. This can improve the performance of complex queries, particularly when working with large amounts of data.
Data Relationships	Foreign keys establish relationships between tables, allowing data to be efficiently linked and retrieved from multiple tables in a database. This is particularly useful for complex data structures where data is spread across multiple tables. It enables efficient data retrieval, avoids data duplication, and provides a way to navigate through related data, enabling the creation of complex reports and queries.
Database Maintenance	Primary and foreign keys are critical for maintaining the integrity of a database. Without these keys, it would be challenging to modify or delete records in a table without affecting the data in other tables. These keys provide a means of maintaining data consistency and avoiding data loss, which is essential for ensuring data reliability and the successful operation of the database.

Further, let us see some articles related to the Difference between Primary Key and Foreign Key: