Primary keys and uniqueness in SQL - Step-by-Step Execution

Concept Flow - Primary keys and uniqueness

Start Table Creation

↓

Define Columns

↓

Set Primary Key?

No→No uniqueness guaranteed

Yes↓

Primary Key: Unique + Not Null

↓

Insert Rows

↓

Check Uniqueness on Insert

↓

Unique

↓

Insert

↓

End

When creating a table, you define a primary key column that must be unique and not null. When inserting data, the database checks this uniqueness and rejects duplicates or nulls.

Execution Sample

SQL

CREATE TABLE Users (
  UserID INT PRIMARY KEY,
  UserName VARCHAR(50)
);

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

This code creates a Users table with UserID as primary key, inserts a row with UserID 1, then tries to insert another row with the same UserID which violates uniqueness.

Execution Table

Step	Action	UserID Value	Uniqueness Check	Result
1	Create table with UserID as PRIMARY KEY	N/A	N/A	Table created
2	Insert row (1, 'Alice')	1	No existing UserID=1	Row inserted
3	Insert row (1, 'Bob')	1	UserID=1 already exists	Insert rejected: duplicate primary key

💡 Insert rejected at step 3 because UserID 1 already exists, violating primary key uniqueness

Variable Tracker

Variable	Start	After Step 2	After Step 3
Users Table Rows	Empty	[{UserID:1, UserName:'Alice'}]	[{UserID:1, UserName:'Alice'}] (no change due to rejection)

Key Moments - 2 Insights

Why can't we insert two rows with the same primary key value?

Because the primary key must be unique for each row. As shown in execution_table step 3, the database rejects the insert to keep uniqueness.

Can a primary key column have NULL values?

No, primary keys cannot be NULL. They must have a value that uniquely identifies each row. This is part of the primary key constraint.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 3 when inserting UserID=1 again?

A The row is inserted successfully

B The insert is rejected due to duplicate primary key

C The existing row is updated

D The insert is ignored silently

💡 Hint

Check the 'Result' column in execution_table row for step 3

According to variable_tracker, what is the state of the Users table after step 3?

A Contains one row with UserID=1

B Is empty

C Contains two rows with UserID=1

D Contains one row with UserID=NULL

💡 Hint

Look at the 'Users Table Rows' row after step 3 in variable_tracker

If the primary key constraint was removed, what would happen at step 3?

A Insert would be rejected

B Insert would cause an error unrelated to uniqueness

C Insert would succeed allowing duplicate UserID

D Insert would update the existing row

💡 Hint

Primary key enforces uniqueness; without it duplicates are allowed

Concept Snapshot

Primary keys uniquely identify each row in a table.
They must be unique and NOT NULL.
Database rejects inserts violating primary key uniqueness.
Use PRIMARY KEY in CREATE TABLE to enforce this.
Uniqueness ensures data integrity and fast lookups.

Full Transcript

This lesson shows how primary keys enforce uniqueness in SQL tables. When you create a table with a primary key column, the database ensures no two rows have the same value in that column and that the value is not null. The example creates a Users table with UserID as the primary key. Inserting a row with UserID 1 succeeds. Trying to insert another row with UserID 1 fails because it violates uniqueness. The execution table traces these steps, showing the insert rejection. The variable tracker shows the table state after each step. Key moments clarify why duplicates and nulls are not allowed in primary keys. The quiz tests understanding of these concepts by referencing the execution visuals.