SQL Primary Key

Introduction

In this chapter, we will learn about the PRIMARY KEY constraint in SQL. The PRIMARY KEY constraint uniquely identifies each record in a database table. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the PRIMARY KEY constraint effectively.

What is a Primary Key?

A primary key is a column or a set of columns in a table that uniquely identifies each row in that table. Each table can have only one primary key, which may consist of single or multiple columns. The primary key column(s) must contain unique values and cannot contain NULL values.

Syntax for Primary Key

Basic Syntax

When creating a table, you can define a column as the primary key using the following syntax:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

Composite Primary Key Syntax

A composite primary key is a primary key that consists of more than one column.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column1, column2)
);

Step-by-Step Example

1. Create a Sample Table with a Primary Key

First, we will create a sample table named employees with a primary key.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 50000),
('Sita', 'Patel', 'sita.patel@example.com', 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 50000);

3. Verify the Primary Key

To verify that the id column is a primary key, we can try to insert a duplicate value:

INSERT INTO employees (id, first_name, last_name, email, salary)
VALUES (1, 'Ravi', 'Verma', 'ravi.verma@example.com', 70000);

This command will result in an error because the id value 1 already exists in the table, demonstrating that the id column enforces unique values.

4. Create a Table with a Composite Primary Key

Now, we will create another table named projects with a composite primary key.

CREATE TABLE projects (
    project_id INT,
    employee_id INT,
    project_name VARCHAR(100),
    PRIMARY KEY (project_id, employee_id)
);

5. Insert Sample Data into the projects Table

INSERT INTO projects (project_id, employee_id, project_name)
VALUES
(1, 1, 'Project A'),
(1, 2, 'Project B'),
(2, 1, 'Project C');

6. Verify the Composite Primary Key

To verify that the combination of project_id and employee_id is a primary key, we can try to insert a duplicate value:

INSERT INTO projects (project_id, employee_id, project_name)
VALUES (1, 1, 'Project D');

This command will result in an error because the combination of project_id 1 and employee_id 1 already exists in the table, demonstrating that the composite primary key enforces unique combinations.

Conclusion

The PRIMARY KEY constraint is a crucial component of relational database design. It ensures that each row in a table is uniquely identifiable, which is essential for maintaining data integrity. This chapter covered the basic syntax for defining primary keys, including composite primary keys, and provided examples to illustrate their use. Understanding how to define and use primary keys effectively will enhance your ability to design robust and reliable database schemas.