What is the difference between UNIQUE and PRIMARY KEY?

A PRIMARY KEY uniquely identifies each row and cannot be NULL. A UNIQUE constraint also enforces uniqueness but allows NULLs (depending on the database) and a table can have multiple UNIQUE constraints.

What does ON DELETE SET NULL do?

When the referenced row in the parent table is deleted, the foreign key column in child rows is set to NULL instead of blocking the delete or cascading it.

Should I add indexes to foreign key columns?

Yes. Most databases do not automatically index foreign key columns. Without an index, joins on the foreign key and ON DELETE operations become full table scans.

Format a SQL CREATE TABLE Statement

CREATE TABLE statements define the schema of your database and should be readable for team review and documentation. This example shows a users table with common constraints, a primary key, foreign key, and index. The formatter aligns column definitions and consistently indents constraint clauses. Keep formatted CREATE TABLE statements in your migration files so the schema history is easy to follow.

Example

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL,
  role ENUM('admin','user','editor') NOT NULL DEFAULT 'user',
  team_id INT UNSIGNED,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL
);

[ open in SQL Formatter → ]

FAQ

What is the difference between UNIQUE and PRIMARY KEY?: A PRIMARY KEY uniquely identifies each row and cannot be NULL. A UNIQUE constraint also enforces uniqueness but allows NULLs (depending on the database) and a table can have multiple UNIQUE constraints.
What does ON DELETE SET NULL do?: When the referenced row in the parent table is deleted, the foreign key column in child rows is set to NULL instead of blocking the delete or cascading it.
Should I add indexes to foreign key columns?: Yes. Most databases do not automatically index foreign key columns. Without an index, joins on the foreign key and ON DELETE operations become full table scans.

Related Examples

Format SQL with Foreign Key Constraints

Referential integrity through foreign keys prevents orphaned records and ensures...

Format a Batch SQL INSERT

Batch INSERT statements with multiple value rows are common in seed files, test ...

Format a SQL SELECT with JOIN

Multi-table JOIN queries quickly become hard to read when written on a single li...