SQL Primary Key

SQL PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each record in a table. A table can have only one primary key, and the primary key value must be unique and NOT NULL.

PRIMARY KEY in CREATE TABLE

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150)
);

PRIMARY KEY as a Separate Definition

CREATE TABLE customers (
  id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150),
  PRIMARY KEY (id)
);

Composite Primary Key

A composite primary key uses multiple columns to uniquely identify each row.

CREATE TABLE subscriptions (
  user_id INT NOT NULL,
  topic_id INT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (user_id, topic_id)
);

PRIMARY KEY vs UNIQUE

• PRIMARY KEY: unique + NOT NULL, and there can be only one per table
• UNIQUE: enforces uniqueness, and you can have multiple UNIQUE constraints per table

Add PRIMARY KEY with ALTER TABLE

You can add a primary key to an existing table (if the column values are unique and not NULL).

ALTER TABLE customers
ADD PRIMARY KEY (id);

Drop PRIMARY KEY (MySQL / MariaDB)

Dropping a primary key is rarely needed, but possible:

ALTER TABLE customers
DROP PRIMARY KEY;

AUTO_INCREMENT Note (MySQL / MariaDB)

AUTO_INCREMENT is commonly used with a numeric primary key to generate ids automatically.

Common Mistakes

• Creating tables without a primary key
• Using a column with duplicates as a primary key
• Trying to add a primary key when NULL values exist

Best Practice

Use a primary key on every table. For many applications, an AUTO_INCREMENT integer is a simple and effective choice.

Next Step

Continue with SQL FOREIGN KEY to learn how to link tables and enforce relationships.