Difference Between Primary Key And Foreign Key
Learn what is primary key and foreign key and explore the major difference between primary key and foreign key.
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
Best-suited IT & Software courses for you
Learn IT & Software with these high-rated online courses
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.
Read Later
Relationship Between Primary Key and Foreign Key
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.
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.
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
Top Picks & New Arrivals
