Primary Key and Foreign Key in SQL

Primary Key and Foreign Key are fundamental concepts in SQL that define relationships between tables and ensure data integrity in relational databases.

What is a Primary Key?

A Primary Key is a column (or a combination of columns) that uniquely identifies each record in a table. It ensures that no duplicate or NULL values can exist in the specified column(s).

Key Points for Primary Key

1. Uniqueness and Non-Null:
   A primary key guarantees that every row in a table is uniquely identifiable. It cannot contain NULL values.

2. Single or Composite Keys:

   • A primary key can consist of a single column.

   • A composite primary key involves two or more columns combined to form a unique identifier.

3. Automatic Indexing:
   Many database systems automatically create an index for the primary key column(s), which helps optimize queries.

Syntax for Primary Key

1. Defining a Primary Key on a Single Column

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

2. Defining a Composite Primary Key

CREATE TABLE project_assignments (
    employee_id INT,
    project_id INT,
    PRIMARY KEY (employee_id, project_id)
);

What is a Foreign Key?

A Foreign Key is a column (or a set of columns) in a table that establishes a link between the data in two tables. It references the primary key in another table, creating a parent-child relationship.

Key Points for Foreign Key

1. Ensures Referential Integrity:
   A foreign key ensures that the values in the child table correspond to values in the parent table. This prevents orphan records in the database.

2. Parent-Child Relationship:
   The table with the foreign key is the child table, and the referenced table is the parent table.

3. Cascading Actions:
   Foreign keys can trigger actions such as ON DELETE CASCADE or ON UPDATE CASCADE, allowing changes in the parent table to reflect in the child table.

Syntax for Foreign Key

1. Defining a Foreign Key

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

2. Using Cascading Actions

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Differences Between Primary Key and Foreign Key

Examples

Primary Key Example

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

Foreign Key Example

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Common Mistakes to Avoid

1. Not Indexing Primary Keys:
   While most databases automatically index primary keys, ensure indexes exist for better performance.

2. Breaking Referential Integrity:
   Ensure foreign key values always reference valid primary key values to prevent orphaned records.

3. Cascading Unintentionally:
   Be cautious when using ON DELETE CASCADE as it can delete related records unintentionally.

By properly defining primary and foreign keys, you can enforce data integrity and establish clear relationships between tables, making your database robust and reliable.