PRIMARY KEY constraint in SQL - Step-by-Step Execution

Concept Flow - PRIMARY KEY constraint

Start Table Creation

↓

Define Columns

↓

Specify PRIMARY KEY Column(s)

↓

Enforce Uniqueness & Not Null

↓

Insert Data

↓

Check for Duplicate or NULL in PK

↓

Accept Row

↓

Table Ready with Unique Rows

↓

End

This flow shows how a PRIMARY KEY is defined during table creation and how it enforces uniqueness and no NULLs when inserting data.

Execution Sample

SQL

CREATE TABLE Students (
  ID INT PRIMARY KEY,
  Name VARCHAR(50)
);

INSERT INTO Students VALUES (1, 'Alice');
INSERT INTO Students VALUES (1, 'Bob');

Creates a Students table with ID as PRIMARY KEY, inserts a row, then tries to insert a duplicate ID which causes an error.

Execution Table

Step	Action	Evaluation	Result
1	Create table Students with ID as PRIMARY KEY	Table schema set with ID unique and NOT NULL	Table created successfully
2	Insert (1, 'Alice')	Check if ID=1 exists and is NOT NULL	Row inserted
3	Insert (1, 'Bob')	Check if ID=1 exists and is NOT NULL	Error: Duplicate PRIMARY KEY value
4	Insert (NULL, 'Charlie')	Check if ID=NULL allowed	Error: PRIMARY KEY cannot be NULL

💡 Insertion stops on duplicate or NULL PRIMARY KEY violation

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4
Students Table Rows	empty	[{ID:1, Name:'Alice'}]	[{ID:1, Name:'Alice'}]	[{ID:1, Name:'Alice'}]
ID Values Present	empty	[1]	[1]	[1]

Key Moments - 2 Insights

Why can't we insert two rows with the same PRIMARY KEY value?

Because the PRIMARY KEY constraint enforces uniqueness, as shown in execution_table step 3 where inserting a duplicate ID causes an error.

Why is NULL not allowed in a PRIMARY KEY column?

PRIMARY KEY columns must uniquely identify rows, and NULL means unknown, so it is disallowed. See execution_table step 4 where inserting NULL ID causes an error.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 3 when inserting (1, 'Bob')?

A Row is inserted successfully

B Row is inserted but with NULL ID

C Error due to duplicate PRIMARY KEY

D Table schema changes

💡 Hint

Check the 'Result' column in execution_table row 3

According to variable_tracker, how many rows are in the Students table after step 2?

A 0

B 1

C 2

D NULL

💡 Hint

Look at 'Students Table Rows' after step 2 in variable_tracker

If we try to insert a row with ID=NULL, what does the PRIMARY KEY constraint do according to execution_table?

A Rejects insertion with error

B Converts NULL to 0

C Allows insertion

D Ignores the PRIMARY KEY constraint

💡 Hint

See step 4 in execution_table for NULL ID insertion

Concept Snapshot

PRIMARY KEY constraint:
- Uniquely identifies each row
- Enforces NOT NULL and uniqueness
- Defined during table creation
- Duplicate or NULL values cause insertion errors
- Essential for data integrity

Full Transcript

The PRIMARY KEY constraint is used in SQL to uniquely identify each row in a table. When creating a table, you specify one or more columns as the PRIMARY KEY. This constraint ensures that no two rows can have the same value in the PRIMARY KEY column(s), and that these values cannot be NULL. During data insertion, the database checks if the PRIMARY KEY value already exists or is NULL. If so, it rejects the insertion with an error. This maintains data integrity by preventing duplicate or missing identifiers. For example, in the Students table, the ID column is the PRIMARY KEY. Inserting a row with ID=1 succeeds the first time but fails if attempted again or if NULL is used. This behavior is critical for reliable database operations.