SQL PRIMARY KEY

What does PRIMARY KEY do in SQL?

PRIMARY KEY defines one or more columns whose values uniquely and non-nullably identify each row in a table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL PRIMARY KEY: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, DB2, DuckDB, and virtually all ANSI-compliant databases

SQL PRIMARY KEY Full Explanation

PRIMARY KEY is a table-level or column-level constraint that enforces three rules: 1) every row must contain a non-NULL value in the key column(s); 2) the combination of values must be unique across the table; 3) the database automatically creates an index to speed lookups and joins that reference the key. A table can have only one PRIMARY KEY, although it can span multiple columns (composite key). The constraint can be declared inline next to a single column or as a separate CONSTRAINT clause that names multiple columns. Because other tables reference the PRIMARY KEY through FOREIGN KEY constraints, it is the cornerstone of relational integrity. Some dialects automatically add AUTOINCREMENT behavior when a single integer column is marked PRIMARY KEY (e.g., SQLite) while others require an explicit identity or sequence. Dropping or changing a PRIMARY KEY usually requires first removing dependent FOREIGN KEY constraints.

SQL PRIMARY KEY Syntax

-- Inline (single column)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table-level (composite key)
CREATE TABLE order_items (
  order_id  INT,
  product_id INT,
  quantity  INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- Add later
ALTER TABLE employees
ADD CONSTRAINT pk_emp PRIMARY KEY (emp_no);

SQL PRIMARY KEY Parameters

Example Queries Using SQL PRIMARY KEY

-- 1. Attempt to insert duplicate primary key
INSERT INTO users (id, username) VALUES (1, 'alice');
INSERT INTO users (id, username) VALUES (1, 'bob'); -- error

-- 2. Reference in another table
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id)
);

-- 3. Drop and recreate
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ADD PRIMARY KEY (username);

Expected Output Using SQL PRIMARY KEY

Second INSERT fails with a unique violation error.
orders table is created and enforces that user_id values exist in users.id.
users table now uses username as its primary key.

Use Cases with SQL PRIMARY KEY

Guarantee each row is uniquely identifiable
Enable fast joins between parent and child tables
Enforce data integrity for transactional systems
Serve as target for FOREIGN KEY references in star-schema designs

Common Mistakes with SQL PRIMARY KEY

Trying to define more than one PRIMARY KEY per table
Allowing NULL values in a key column (not permitted)
Assuming PRIMARY KEY and UNIQUE are identical (PRIMARY KEY also implies NOT NULL)
Forgetting to index large composite keys, leading to poor performance on some engines
Relying on natural keys that later change, causing cascading updates

First Introduced In

SQL-86 (formalized in SQL-92)

Frequently Asked Questions

Can a table have multiple primary keys?

No. A table can contain only one PRIMARY KEY, though that key can span several columns.

Is a primary key automatically indexed?

Yes. All major database engines create a unique index behind the scenes when you declare a PRIMARY KEY.

PRIMARY KEY vs UNIQUE: what is the difference?

PRIMARY KEY enforces uniqueness and NOT NULL and is the default target for FOREIGN KEY references, while UNIQUE enforces only uniqueness.

How can I modify an existing primary key?

Drop the current PRIMARY KEY constraint, ensure no foreign keys or duplicate data block the change, then add the new PRIMARY KEY on the desired column(s).