SQL PRIMARY KEY Constraint

What Is a PRIMARY KEY in SQL?

A PRIMARY KEY is a column—or a combination of columns—that uniquely identifies each row in a table. It prevents duplicate values and ensures every row can be referenced reliably.

Key Properties

Must be unique across all rows
Cannot contain NULL values
Each table can have only one PRIMARY KEY
Often used as a reference for FOREIGN KEY relationships

Defining a PRIMARY KEY

Column-Level Definition

Define a primary key directly on a single column:

Table-Level Definition

Define a primary key using a separate constraint:

Composite Primary Key

Use multiple columns to uniquely identify rows:

Adding a PRIMARY KEY to an Existing Table

You can add a primary key constraint to an existing table using ALTER TABLE:

Best Practices

Tips for Using PRIMARY KEYs

Use INTEGER primary keys for better performance and simpler joins
Avoid using changeable data (like email or username) as primary keys
Consider using auto-incrementing values for primary keys
Keep composite primary keys simple - fewer columns are better
Always define a primary key for your tables - it helps maintain data integrity

Common Interview Questions

Q: What's the difference between a PRIMARY KEY and a UNIQUE constraint?

A: While both ensure uniqueness, a PRIMARY KEY:

Cannot contain NULL values
Can only exist once per table
Is typically used as the main identifier for rows
Is automatically indexed

Q: When should you use a composite primary key?

Use composite primary keys when a single column cannot uniquely identify a row, or when the natural relationship between columns forms a unique identifier. However, in our example database, we use single-column primary keys (user_id, video_id, interaction_id) for simplicity and better performance.

Interview Master

SQL Learning Hub