SQL Primary Key

Understanding SQL Primary Key

In SQL, a Primary Key is a constraint that uniquely identifies each record in a table. It ensures that no two rows in a table have the same value in the primary key column(s). This constraint is essential for maintaining data integrity and establishing relationships between tables in a relational database.

Key Features of a Primary Key

1. Uniqueness: Each value in the primary key column must be unique across the table.
2. Non-Null: A primary key column cannot contain NULL values.
3. Single or Composite Key: A primary key can consist of one column (single key) or multiple columns (composite key).
4. Automatically Indexed: Most database systems automatically create an index for the primary key, improving query performance.
5. Only One Primary Key: A table can have only one primary key, but it can consist of multiple columns.

Syntax for Defining a Primary Key

You can define a primary key while creating a table or by modifying an existing table.

1. Define a Primary Key During Table Creation

Single-Column Primary Key:

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

Composite Primary Key:

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

2. Add a Primary Key to an Existing Table

Single-Column:

ALTER TABLE employees
ADD CONSTRAINT pk_employee PRIMARY KEY (employee_id);

Composite Key:

ALTER TABLE order_details
ADD CONSTRAINT pk_order_product PRIMARY KEY (order_id, product_id);

Examples of Primary Key Usage

1. Creating a Table with a Primary Key

Create a students table with a student_id column as the primary key:

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

Result:

• The student_id column must have unique, non-NULL values.

2. Composite Primary Key

Create a course_enrollments table where each record is uniquely identified by a combination of student_id and course_id.

CREATE TABLE course_enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

Result:

• Each combination of student_id and course_id must be unique.

3. Adding a Primary Key to an Existing Table

Add a primary key to an existing products table.

ALTER TABLE products
ADD CONSTRAINT pk_product_id PRIMARY KEY (product_id);

Result:

• The product_id column becomes the primary key for the products table.

Primary Key vs. Unique Key

Feature	Primary Key	Unique Key
Uniqueness	Ensures all values are unique	Ensures all values are unique
NULL Values	Does not allow NULL	Allows one NULL value (depending on the database)
Per Table	Only one primary key allowed	Multiple unique keys allowed

Primary Key in Relationships

Primary keys are crucial in establishing relationships between tables. For instance:

1. One-to-Many Relationship:
   A the departments table has a primary key department_id, which is referenced as a foreign key in an employees table.

   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. Many-to-Many Relationship:
   A students table and a courses table are linked via a course_enrollments table with a composite primary key.

Best Practices for Primary Keys

1. Use Integer IDs: Use integers for primary keys as they are efficient for indexing and querying.
2. Keep Primary Keys Simple: Avoid using composite primary keys unless necessary.
3. Avoid Changing Primary Keys: Once set, avoid modifying primary key values as they are often referenced in other tables.
4. Use Descriptive Names: Name primary keys with clear and consistent naming conventions (e.g., employee_id, product_id).

Common Errors with Primary Keys

1. Duplicate Values: Inserting duplicate values into a primary key column results in an error.

   INSERT INTO employees (employee_id, name, department) VALUES (1, 'John Doe', 'HR');
   INSERT INTO employees (employee_id, name, department) VALUES (1, 'Jane Doe', 'Finance'); -- Error
   

2. NULL Values: Attempting to insert a NULL value into a primary key column results in an error.

   INSERT INTO employees (employee_id, name, department) VALUES (NULL, 'John Doe', 'HR'); -- Error
   

Conclusion

The SQL Primary Key is a cornerstone of relational database design. It ensures data integrity by uniquely identifying each record and plays a vital role in creating relationships between tables. By adhering to best practices, you can create robust and scalable database designs that minimize errors and improve performance.