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.
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, ...);
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.