SQL - Primary Key in SQL

What Is a Primary Key in SQL?

A Primary Key is a column (or set of columns) in a database table that uniquely identifies each row in that table. It is one of the most important concepts in relational database design.

 Characteristics of a Primary Key

  1. Unique: Every value in the primary key column(s) must be unique.

  2. Not Null: Primary keys cannot contain NULL values—each record must have a valid, unique identifier.

  3. Single or Composite: A primary key can consist of:

    • A single column (e.g., id)

    • Multiple columns (composite key), e.g., order_id + product_id

  4. Automatically Indexed: Most databases automatically create an index on the primary key to speed up searches.

Syntax Example – Single Column

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(100),
  email VARCHAR(100)
);

  • user_id is the primary key.

  • It must be unique and not null for each user.

Syntax Example – Composite Key

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

  • This means that each combination of order_id and product_id must be unique.

  • Useful when one column alone is not enough to uniquely identify a row.

Why Is a Primary Key Important?

  • Ensures uniqueness of each row in a table.

  • Supports relationships with other tables through foreign keys.

  • Helps enforce data integrity.

  • Makes searches and lookups faster via indexing.

Good Practices for Primary Keys

  • Use a simple, numeric key (like an auto-incremented id) whenever possible.

  • Avoid using personal data (e.g., emails, names) as a primary key.

  • Never change the value of a primary key once it's set (it can break relationships).