Primary Key - SQL

In SQL, a primary key is a column or a set of columns in a table that uniquely identifies each row in the table. It ensures data integrity and provides a reference point for establishing relationships with other tables in a relational database.

Key Characteristics of Primary Keys:

1. Uniqueness: Each value in the primary key column(s) must be unique within the table. This uniqueness constraint prevents duplicate rows in the table.
2. Non-null: The values in the primary key column(s) cannot be NULL. This ensures that every row in the table is uniquely identifiable.
3. Immutable: Once a primary key value is assigned to a row, it should not change. This maintains data integrity and consistency.

Syntax for Defining Primary Key

The primary key constraint is defined when creating or altering a table. In most SQL dialects, you can specify the primary key constraint inline with the column definition or separately using the PRIMARY KEY keyword.

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

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

Example

Consider a table named "employees" with columns "employee_id", "first_name", and "last_name". If "employee_id" uniquely identifies each employee, you can define it as the primary key:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Benefits of Primary Keys:

1. Data Integrity: Primary keys ensure the uniqueness and integrity of data within a table, preventing duplicate or inconsistent records.
2. Indexing: Primary keys are often automatically indexed by the database system, which improves query performance for data retrieval and manipulation.
3. Relationships: Primary keys serve as reference points for establishing relationships (e.g., foreign key constraints) between tables, enabling data normalization and enforcing data integrity rules.

Conclusion

In SQL, a primary key is a fundamental concept used to uniquely identify rows in a table. By enforcing uniqueness and data integrity, primary keys play a crucial role in database design, indexing, and establishing relationships between tables. Understanding how to define and utilize primary keys is essential for building robust and efficient relational database schemas.