What is the Difference Between Foreign key and Primary key?

The main difference between a primary key and a foreign key lies in their purpose and functionality within a database table. Here are the key differences between the two:

1. Purpose:

• Primary key: Used to uniquely identify each record in a table and ensure data integrity within the table.
• Foreign key: Establishes relationships between tables by referencing the primary key of another table, ensuring data integrity and consistency between related tables.

1. Uniqueness:

• Primary key: Values in a primary key column must be unique, and a table can have only one primary key.
• Foreign key: Multiple foreign keys may exist in a table, and they can accept multiple null values.

1. NULL Values:

• Primary key: Does not accept NULL values.
• Foreign key: May accept multiple NULL values.

1. Constraints:

• Primary key: Combination of UNIQUE and NOT NULL constraints.
• Foreign key: Can be any combination of constraints, depending on the relationship between tables.

1. Deletion and Insertion:

• Primary key: Value cannot be removed from the parent table.
• Foreign key: Value can be deleted from the child table.

1. Impact on Database Management:

• Primary key: Identifies each record in a table and helps in database management.
• Foreign key: Connects related tables and ensures referential integrity.

In summary, primary keys are used to uniquely identify records within a table and maintain data integrity within that table, while foreign keys establish relationships between tables by referencing the primary key of another table. They both play crucial roles in maintaining data integrity, consistency, and relational structure in a database.

Comparative Table: Foreign key vs Primary key

Here is a table that highlights the differences between foreign keys and primary keys:

In summary, a primary key is used to ensure that data in a specific column is unique and identifies a record in the table, while a foreign key is used to create a relationship between two tables and refers to the primary key of another table.