Mastering PRIMARY KEY Constraints in SQL: A Complete Guide

Definition: A primary key is a minimal set of attributes (columns) in a table that uniquely identifies tuples (rows) in that table.

Why we need a Key?

In real world applications, number of tables required for storing the data is huge, and the different tables are related to each other as well.

Also, tables store a lot of data in them. Tables generally extends to thousands of records stored in them, unsorted and unorganised.

Now to fetch any particular record from such dataset, you will have to apply some conditions, but what if there is duplicate data present and every time you try to fetch some data by applying certain condition, you get the wrong data. How many trials before you get the right data?

Using PRIMARY KEY constraint at Table Level

Example:

Code:

CREATE table Student (
std_id int PRIMARY KEY,
Name varchar(60) NOT NULL,
Age int
);

The above command will creates a PRIMARY KEY on the std_id.

Example:

Code:

ALTER table Student ADD PRIMARY KEY (std_id);

The above command will creates a PRIMARY KEY on the std_id.

Points to Remember:

• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.
• A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).