SQL - Primary Key

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 the integrity and uniqueness of data by enforcing the following rules:

• Each row must have a unique value in the primary key column(s).
• A primary key value cannot be NULL.
• There can be only one primary key defined per table.

The primary key is used to establish a relationship between tables and provide a way to uniquely identify and access individual rows in a table. It is commonly used to enforce data integrity and support data retrieval operations.

Syntax:

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

Alternatively, you can define a primary key constraint after creating the table using the ALTER TABLE statement:

ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2, ...);

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Grade VARCHAR(10)
);

In this example, the StudentID column is designated as the primary key using the PRIMARY KEY keyword. It ensures that each student has a unique ID value.

By defining the primary key, we enforce the uniqueness and non-nullability of the StudentID column. This means that each row in the Students table must have a unique StudentID value, and the StudentID value cannot be NULL.

The primary key constraint allows you to perform various operations, such as:

• Ensuring data integrity: The primary key constraint prevents duplicate rows and enforces the uniqueness of each row in the table.
• Referencing in other tables: You can use the primary key as a reference in other tables to establish relationships, such as foreign key constraints.
• Fast data retrieval: Searching and accessing specific rows based on their primary key values is efficient, as the primary key is typically indexed.