Primary Key Constraint in SQL

Primary Key Constraint in SQL

The Primary Key constraint is essential in relational databases. It ensures that each row in a table is unique and easily identifiable. Understanding how to define and enforce primary keys is crucial for designing well-structured databases.

1. What is a Primary Key?

  • Definition: A primary key is a column (or a combination of columns) in a table that uniquely identifies each row in that table. The primary key must contain unique values, and it cannot contain NULL values.

  • Purpose: The primary key serves as the unique identifier for each record in the table. It ensures that no two rows have the same primary key value, maintaining data integrity and making it easy to retrieve and reference specific records.

Key Characteristics:

  1. Uniqueness: Every value in the primary key column(s) must be unique. No two rows can share the same primary key value.

  2. Non-NULL: The primary key column(s) cannot contain NULL values. Every row must have a valid value for the primary key.

  3. Single or Composite: A primary key can be a single column (simple primary key) or a combination of columns (composite primary key).

2. Defining a Primary Key

You define a primary key when you create a table. The primary key can be defined on a single column or a combination of columns. Let's explore both cases.

a. Defining a Primary Key on a Single Column

When defining a primary key on a single column, you can use the PRIMARY KEY keyword directly after the column definition.

Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birthdate DATE
);

In this example:

  • The student_id column is defined as the primary key. This means that each student in the students table will have a unique student_id, and no student_id can be NULL.

Here’s an example of inserting data with Indian names:

INSERT INTO students (student_id, first_name, last_name, birthdate)
VALUES (1, 'Amit', 'Sharma', '2001-04-15');

INSERT INTO students (student_id, first_name, last_name, birthdate)
VALUES (2, 'Neha', 'Singh', '2000-08-10');

  • In this case, Amit Sharma and Neha Singh have unique student_id values, ensuring each record is distinct.

b. Defining a Composite Primary Key

Sometimes, a single column may not be enough to uniquely identify a row. In such cases, you can use a combination of columns as the primary key. This is known as a composite primary key.

Example:

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

In this example:

  • The enrollments table tracks which students are enrolled in which courses. The combination of student_id and course_id is used as the primary key, meaning that each student can enroll in multiple courses, but they cannot enroll in the same course more than once.

Here’s an example of inserting data with Indian names:

INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES (1, 101, '2023-06-01'); -- Amit Sharma enrolls in course 101

INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES (2, 102, '2023-06-01'); -- Neha Singh enrolls in course 102

  • In this case, each enrollment is unique because it’s based on the combination of student_id and course_id.

3. Enforcing Primary Keys

Once a primary key is defined, the database automatically enforces the rules of uniqueness and non-nullability. Let’s explore how this works.

a. Uniqueness Enforcement

When you insert or update a record, the database checks that the primary key value is unique. If you try to insert a row with a duplicate primary key value, the database will throw an error.

Example:

INSERT INTO students (student_id, first_name, last_name, birthdate)
VALUES (1, 'Rahul', 'Mehta', '2002-11-25');

-- Attempting to insert another row with the same student_id
INSERT INTO students (student_id, first_name, last_name, birthdate)
VALUES (1, 'Sonia', 'Kapoor', '2001-12-15');

In this example:

  • The first INSERT statement succeeds, adding a student with student_id 1.

  • The second INSERT statement fails because student_id 1 already exists in the table.

b. Non-NULL Enforcement

The database also ensures that primary key columns cannot contain NULL values. If you attempt to insert a row with a NULL primary key, the database will throw an error.

Example:

-- Attempting to insert a row with a NULL student_id
INSERT INTO students (student_id, first_name, last_name, birthdate)
VALUES (NULL, 'Pooja', 'Verma', '2003-05-10');

In this example:

  • The INSERT statement fails because student_id, the primary key, cannot be NULL.

4. Altering Primary Keys

If you need to add or modify a primary key after creating the table, you can use the ALTER TABLE command. However, altering primary keys can be complex, especially if there is existing data.

Adding a Primary Key to an Existing Table

ALTER TABLE students
ADD PRIMARY KEY (student_id);

This command adds a primary key to the student_id column in the students table if it wasn't already defined.

Dropping a Primary Key

If you need to remove a primary key from a table:

ALTER TABLE students
DROP PRIMARY KEY;

This command removes the primary key constraint from the students table.

5. Benefits of Using Primary Keys

  • Data Integrity: Ensures that each record in the table is unique and easily identifiable, preventing duplicate records.

  • Efficient Access: Primary keys enable faster searches, updates, and deletions since the database can quickly locate the unique row using the primary key.

  • Relationships: Primary keys are essential for creating relationships between tables using foreign keys, enabling the design of normalized databases.

Conclusion

The primary key constraint is a crucial component of relational databases. By defining and enforcing primary keys, you ensure that each row in your table is unique and non-null. Whether you're using a single column or a composite key, understanding how to work with primary keys is fundamental to maintaining data integrity and designing efficient database structures.

This tutorial is brought to you by codes with pankaj.

PreviousDropping Tables and Databases in SQLNextForeign Key Constraint in SQL

Last updated