Primary Keys and Uniqueness

Understanding how a relational database ensures data integrity starts with primary keys. A primary key is a column, or a set of columns, in a table whose values uniquely identify each row. The primary key serves several essential purposes:

• It guarantees that every row is distinct;
• It prevents duplicate data;
• It acts as the main reference point for relationships between tables.

Without a primary key, it becomes difficult to reliably retrieve, update, or delete specific records, and the risk of duplicate or ambiguous data increases. For these reasons, every table should have a primary key defined.

-- Create a 'courses' table with a primary key on 'course_id'
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    course_code VARCHAR(20),
    credits INT
);

By defining a primary key, you instruct the database to enforce uniqueness for that column or combination of columns. This means that no two rows can have the same primary key value. The database automatically creates an index on the primary key, which makes searching for rows by their key values fast and efficient. When you use primary keys, you can quickly retrieve, update, or delete a specific row without scanning the entire table. This combination of uniqueness and performance is why primary keys are fundamental to reliable and efficient database design.