Difference Between Primary Key And Foreign Key

Difference Between Primary Key And Foreign Key

4 mins read1.1K Views Comment
Rashmi
Rashmi Karan
Manager - Content
Updated on Oct 12, 2023 16:13 IST

Learn what is primary key and foreign key and explore the major difference between primary key and foreign key.

2023_01_difference-between-primary-key-and-foreign-key..jpg

Keys are vital components of the relational database schema because they establish a link between two different tables and ensure that any row of data within a table is uniquely identified. However, keys have more specific importance than just maintaining links. They help to collect specific data from numerous rows that would have been convoluted. Primary and foreign keys are the two primary keys that help establish and identify links between relational databases efficiently.

You can also explore: Database and SQL Online Courses & Certifications

Difference Between Primary And Foreign Keys

The main difference between primary and foreign keys is that the primary key identifies a unique table record, while the foreign key relates the data in one table to another or a different record in the same table.

Primary key Foreign key
Duplicate Values Primary keys do not allow two rows to have the same values. Foreign keys allow the two rows to have the same values.
Insertion Values ​​can be inserted even if the foreign key does not have that value. Values​ cannot be inserted if the values ​​are not present in the primary key.
Range Each relational database table row can have only one primary key. A relational database table can have numerous foreign keys.
Clustered index Primary keys, by default, have a clustered index. Foreign keys do not have a clustered index.
Suppression When a value is to be removed, the value must be made to not already be present in the foreign key reference table. When a value is removed, it can quickly be done from foreign keys.
Temporary Tables Primary keys can be defined in a temporary table. Foreign keys cannot be defined in a temporary table.

You can also explore: 100+ SQL Interview Questions and Answers for 2022

What is the Primary Key?

A primary key refers to a particular choice of columns that uniquely determines the set of rows in a table. A primary key is a unique attribute with a unique ID and is also considered a candidate key. 

There are two primary keys, i.e., a simple primary key and a primary compound key. 

A simple primary key is a database table made up of a single column, while a primary compound key is a database table with more than one column.

You can also explore: Find Nth highest salary in SQL

What is a Foreign Key?

A foreign key refers to a column in a database table that provides a link between two tables. The primary task of the foreign key is to ensure the referential integrity of the data. In other words, only values ​​that are expected to appear in the database are allowed.

Unlike primary keys, foreign keys can contain null values, as they don’t help identify a difference in the relationship because the primary keys have already done this work. Foreign keys can also accept duplicate values​​, unlike the primary key, and can have many or multiple foreign keys in a database since foreign keys can have different attributes. However, foreign keys cannot have a clustered index.

All About DML Commands in SQL
All About DML Commands in SQL
In SQL there are five different types of statements (DDL, DQL, DML, DCL and TCL) that are used while writing the queries. In this article, we will briefly discuss different...read more
What are Constraints in SQL?
What are Constraints in SQL?
Constraints in SQL are not a mandatory to use while creating the table but they are very helpful to maintain the accuracy, reliability, and integrity of the data during the...read more

Relationship Between Primary Key and Foreign Key

2023_01_primary-key-vs-foreign-key.jpg

In the above table, the customer_id field in the Orders table is FOREIGN KEY which references the customer_id field in the Customers table.

The customer_id (Orders table) value must be from the id column (Customers table).

Primary Key vs Foreign Key

1. The primary key uniquely defines a tuple in a relation. At the same time, the foreign key in one table refers to the primary key of other tables.

2. No two tuples in a relation carry duplicate values ​​for a primary key attribute while the foreign key is nullable.

3. A primary key uniquely identifies a record in the relational database table.

4. A primary key is a combination of unique and non-null constraints, so duplicate values ​​cannot be allowed in a primary key field in a relational database table.

5. You can remove a value from the foreign key column without being concerned about whether that value is present in the referenced primary key column of the referenced relationship.

6. The foreign key does not automatically create an index, clustered or nonclustered. You can manually create an index on the foreign key. In the primary key constraint situation, a clustered index creates automatically when defining it.

Comparable vs Comparator
Comparable vs Comparator
Comparable and comparator both are types of interfaces. But, both of these interfaces work differently; hence, comparable vs comparator interfaces have differences. The main difference between comparable vs comparator is...read more
What is the Difference Between DBMS and RDBMS?
What is the Difference Between DBMS and RDBMS?
DBMS (Database Management System) is a software tool that manages databases, while RDBMS (Relational Database Management System) is a type of DBMS that organizes data into tables with predefined relationships....read more

Conclusion

In the database management system, keys play an important role in establishing relationships within a table and also between different tables. However, to achieve this, ensure that the areas you use to maintain relationships between different tables must have comparable values​​, and the table must be made up of unique rows.

A primary key uses the data within a column or multiple columns to uniquely identify all rows in a relational database table to avoid inserting a duplicate row of data. In contrast, a foreign key establishes a link between two tables in a database.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio