In relational databases like SQL Server, MySQL, PostgreSQL, or Oracle, data integrity is crucial. One of the key constraints that helps maintain integrity is the Primary Key. It uniquely identifies each row in a table, ensuring that no two records are the same in the column(s) marked as a primary key.
Key Properties of a Primary Key:
- Uniquely identifies each record.
- Does not allow NULL values.
- Ensures that no duplicate values exist in the specified column(s).
Let’s see a practical example that demonstrates how it enforces uniqueness.
Let’s take an example of a Employee table where the EmpId column is defined as the primary key.
CREATE TABLE dbo.Employee
(Empid INT PRIMARY KEY,
Name NVARCHAR(100),
Age INT,
Joining_Date DATE
)
GO
Inserting First record with EMPID – 12345 for Employee Name John Adam – Successful (Unique Record)
INSERT INTO dbo.Employee (Empid, Name, Age,Joining_Date)
VALUES (12345, 'John Adam', 25,'2024-06-27');
GO
Inserting second record with same EmpID – 12345 for different employee Name Johm Smith – Insert Fails (Duplicate Primary Key)
Below is the error:
Violation of PRIMARY KEY constraint ‘PK__Employee__AF2EBFA1316464D9’. Cannot insert duplicate key in object ‘dbo.Employee’. The duplicate key value is (12345).
Hence, a primary key plays a vital role in ensuring that every record in a table remains unique, consistent, and easily identifiable