PRIMARY KEY
A PRIMARY KEY is a constraint used to uniquely identify each record in a SQL Server table. A primary key does not allow duplicate values and does not allow NULL values.
What is a PRIMARY KEY?
A PRIMARY KEY is one of the most important constraints in SQL Server. It identifies each row in a table uniquely.
A table can have only one PRIMARY KEY constraint, but the primary key can contain one column or multiple columns.
Example
In a Students table, StudentId can be used as the PRIMARY KEY because every student should have a unique StudentId.
PRIMARY KEY Rules
- A PRIMARY KEY uniquely identifies each record.
- Duplicate values are not allowed.
- NULL values are not allowed.
- A table can have only one PRIMARY KEY constraint.
- A PRIMARY KEY can contain one or multiple columns.
Creating a PRIMARY KEY
A PRIMARY KEY can be defined while creating a table.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
Age INT
);
Here, StudentId is the PRIMARY KEY of the Students table.
Output
| StudentId | StudentName | Age |
|---|---|---|
| 101 | Rahul | 22 |
| 102 | Priya | 21 |
| 103 | Amit | 23 |
Each StudentId is unique, so it can identify a student without ambiguity.
Duplicate Values are Not Allowed
Because StudentId is a PRIMARY KEY, the same StudentId cannot be inserted twice.
INSERT INTO Students
VALUES (101, 'Amit', 24);
NULL Values are Not Allowed
A PRIMARY KEY column cannot contain NULL values.
INSERT INTO Students
VALUES (NULL, 'Rahul', 22);
Named PRIMARY KEY Constraint
We can give a specific name to the PRIMARY KEY constraint.
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
Age INT,
CONSTRAINT PK_Students
PRIMARY KEY (StudentId)
);
Add PRIMARY KEY to Existing Table
If a table already exists, a PRIMARY KEY can be added using ALTER TABLE.
ALTER TABLE Students
ADD CONSTRAINT PK_Students
PRIMARY KEY (StudentId);
Composite PRIMARY KEY
A composite primary key uses two or more columns together to uniquely identify a record.
CREATE TABLE StudentCourses
(
StudentId INT,
CourseId INT,
CONSTRAINT PK_StudentCourses
PRIMARY KEY (StudentId, CourseId)
);
In this example, the combination of StudentId and CourseId must be unique.
PRIMARY KEY Structure
Students Table
|
↓
StudentId
|
↓
PRIMARY KEY
|
├── Unique
|
└── NOT NULL
|
↓
Uniquely Identifies Student
Verify PRIMARY KEY
The following query can be used to view the primary key information of a table.
EXEC sp_help 'Students';
Important Points
- PRIMARY KEY uniquely identifies a row.
- Duplicate PRIMARY KEY values are not allowed.
- PRIMARY KEY cannot contain NULL values.
- Only one PRIMARY KEY constraint can exist per table.
- A PRIMARY KEY can contain multiple columns.
Summary
In this section, we learned about PRIMARY KEY, its rules, how to create it, how to add it to an existing table and how composite primary keys work.