Difference Between Primary Key VS Foreign Key
What is a Primary Key?
A primary key is a unique identifier for a specific row in a table in a database. It is used to ensure data integrity and to prevent duplicate entries. It can be one column or a combination of multiple columns.
It is used to quickly look up and retrieve specific rows from the table. Primary keys must contain unique values and cannot contain null values.
Here are three examples of how a primary key might be used:
- In a database for a retail store, the
id
field in thecustomers
table could be the primary key. This would ensure that each customer in the table is assigned a unique identification number, which could be used to quickly look up information about a particular customer. - In a database for a library, the
book_id
field in thebooks
table could be the primary key. This would ensure that each book in the library’s collection is assigned a unique identification number, which could be used to quickly look up information about a particular book. - In a database for a school, the
student_id
field in thestudents
table could be the primary key. This would ensure that each student at the school is assigned a unique identification number, which could be used to quickly look up information about a particular student.
What is a Foreign Key?
A foreign key is a field in a database table that is used to uniquely identify a row in another table. Foreign keys are used to establish and enforce relationships between tables in a database, such as the relationship between a customer and an order or a product and a supplier.
Here are three examples of how a foreign key might be used:
- In a database for a retail store, the
customer_id
field in theorders
table could be a foreign key that references theid
field in thecustomers
table. This would ensure that every order in theorders
table is associated with a valid customer in thecustomers
table. - In a database for a library, the
book_id
field in theborrowers
table could be a foreign key that references theid
field in thebooks
table. This would ensure that every book borrowed by a patron is a valid book in the library’s collection. - In a database for a school, the
student_id
field in thegrades
table could be a foreign key that references theid
field in thestudents
table. This would ensure that every grade in thegrades
table is associated with a valid student at the school.
Primary Key Vs Foreign Key
- A primary key is a column or set of columns that uniquely identifies each row in a table. This means that no two rows in the table can have the same primary key value while a foreign key is a column or set of columns in one table that refers to the primary key of another table.
- The primary key is used to enforce the integrity of the data and to quickly look up and retrieve specific rows from the table and the purpose of a foreign key is to establish a link between the data in two tables, so that changes in one table will affect the data in another table.
- Primary keys must contain unique values and cannot contain null values. The primary key is also used as a reference point for other tables in the database, through the use of foreign keys while foreign key is used to enforce referential integrity, which ensures that data in one table is consistent with data in another table.
- EXAMPLE: If you have a table called “orders” and another table called “customers”, the primary key in the “customers” table would be the customer ID, and the foreign key in the “orders” table would be the customer ID, linking the order to the customer. This way when you make changes to the customer data (e.g. updating the customer address) it will also affect all the orders that were made by that customer.
More to Read
- Relational Database Benefits and Limitations
- Relational Vs Non Relational Database
- Data Warehouse vs Database
- Dataset vs Database
- Database vs DataFrame
- Primary Key vs Foreign Key
- Primary Key vs Candidate Key
- 13 Examples of Relational Database
- Relational Database Vs. Object-Oriented Database
- 9 Types of Databases
- Distributed Database
- Operational Database
- Personal Database
- Graph Databases
- Centralized Database