Difference Between Primary Key and Foreign Key

Primary Key	Foreign Key
Used to uniquely identify each record in a database table.	Used to link two tables together; a foreign key in one table refers to the primary key in another table.
The primary key column value can never be NULL.	The foreign key column can accept NULL values.
A table can have only one primary key.	A table can have multiple foreign keys.
The primary key is unique; it cannot store duplicate values.	The foreign key can store duplicate values.
The primary key is a clustered index by default, meaning it is automatically indexed.	A foreign key is not automatically indexed as a clustered index; it can be manually indexed if needed.
The primary key cannot be deleted if it is being referenced by a foreign key. To delete, the referencing foreign key must not contain its value.	The foreign key value can be deleted without affecting the primary key it refers to.
You can insert values into the primary key column without restrictions, whether or not they are present in the foreign key column.	A foreign key value must already exist in the referenced primary key column to be inserted.
Primary key constraints can be defined on temporary tables.	Foreign key constraints cannot be defined on temporary tables.
Cannot create a parent-child relationship in a table.	Creates a parent-child relationship, where the primary key is the parent and the foreign key is the child.

Key Points to Note:

• Basic Purpose:
• Primary Key: Ensures that each record in a table is uniquely identified.
• Foreign Key: Establishes a relationship between two tables by referring to the primary key of another table.
• NULL Values:
• Primary Key: The primary key column cannot have NULL values to maintain uniqueness.
• Foreign Key: The foreign key column can accept NULL values, indicating that a record can optionally not be linked to another record.
• Count:
• Primary Key: A table can only have one primary key, which can be a combination of columns (composite key).
• Foreign Key: A table can have multiple foreign keys, each referring to different primary keys.
• Duplication:
• Primary Key: The primary key enforces uniqueness and does not allow duplicates.
• Foreign Key: A foreign key allows duplicate values, as multiple records in the child table can refer to the same record in the parent table.
• Indexing:
• Primary Key: Automatically indexed and creates a clustered index, ensuring fast data retrieval.
• Foreign Key: Not automatically indexed as a clustered index, but manual indexing can be done for performance optimization.
• Deletion:
• Primary Key: Cannot be deleted if it is being referenced by a foreign key in another table.
• Foreign Key: Can be deleted even if it refers to a primary key in another table (unless there are specific constraints like ON DELETE RESTRICT or ON DELETE CASCADE).
• Insertion:
• Primary Key: Values can be inserted without any restrictions.
• Foreign Key: Values inserted into the foreign key column must exist in the primary key column of the referenced table, ensuring referential integrity.
• Temporary Tables:
• Primary Key: Can be defined on temporary tables.
• Foreign Key: Cannot be defined on temporary tables because foreign keys require permanent tables for enforcing referential integrity.
• Relationship:
• Primary Key: Does not create a parent-child relationship within a table; it simply uniquely identifies records.
• Foreign Key: Creates a parent-child relationship between tables, where the primary key is the parent, and the foreign key in the child table references the primary key.

Example of Primary and Foreign Key Usage:

1. Primary Key Example:

CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50)

);

2. Foreign Key Example:

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    order_date DATE,

    employee_id INT,

    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)

);

In this example, the employees table has a primary key (employee_id), while the orders table has a foreign key (employee_id) referencing the employee_id in the employees table.

Would you like to explore more details about primary or foreign keys?