Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

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.

SQL
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.

SQL
INSERT INTO Students
VALUES (101, 'Amit', 24);
Error: The INSERT operation fails because StudentId 101 already exists as a PRIMARY KEY value.

NULL Values are Not Allowed

A PRIMARY KEY column cannot contain NULL values.

SQL
INSERT INTO Students
VALUES (NULL, 'Rahul', 22);
Error: A NULL value cannot be inserted into a PRIMARY KEY column.

Named PRIMARY KEY Constraint

We can give a specific name to the PRIMARY KEY constraint.

SQL
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.

SQL
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.

SQL
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

SQL Server

                                                     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.

SQL
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.