SQL PRIMARY

What does SQL PRIMARY KEY do?

PRIMARY designates a column or set of columns as the table’s primary key, enforcing uniqueness and non-nullability.

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: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, DB2, and most ANSI-compliant databases

SQL PRIMARY Full Explanation

PRIMARY KEY is a table-level or column-level constraint that uniquely identifies each row in a table. Every table can have only one primary key, although it may be composite (multiple columns). When defined, the database automatically creates an associated unique index to enforce key integrity and speed lookups. All columns that participate must be declared NOT NULL implicitly or explicitly. Primary keys are referenced by foreign keys in other tables to maintain referential integrity. Attempting to insert duplicate or null values in a primary-key column results in an error. While you may drop and recreate a primary key, doing so can invalidate dependent foreign keys until the constraint is restored.

SQL PRIMARY Syntax

-- Column-level definition
CREATE TABLE table_name (
    column_name data_type PRIMARY KEY,
    ...
);

-- Table-level (composite) definition
CREATE TABLE table_name (
    col1 data_type NOT NULL,
    col2 data_type NOT NULL,
    col3 data_type,
    PRIMARY KEY (col1, col2)
);

-- Add later with ALTER TABLE
ALTER TABLE table_name
ADD CONSTRAINT pk_name PRIMARY KEY (col1, col2);

SQL PRIMARY Parameters

constraint_name (string) - Optional name assigned to the primary key constraint.
column_list (identifier list) - One or more columns that form the key, separated by commas.

Example Queries Using SQL PRIMARY

-- Simple primary key
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);

-- Add a primary key after table creation
ALTER TABLE sessions
ADD CONSTRAINT pk_sessions PRIMARY KEY (session_id);

Expected Output Using SQL PRIMARY

Tables are created with an automatically generated unique index on the primary key column(s)
Subsequent INSERT or UPDATE statements that violate uniqueness or attempt to set key columns to NULL will fail with an error

Use Cases with SQL PRIMARY

Guarantee each row is uniquely identifiable.
Speed up joins and lookups on the key column(s).
Form the parent side of a foreign key relationship.
Prevent accidental duplicate data during bulk imports.

Common Mistakes with SQL PRIMARY

Defining more than one primary key on the same table (only one is allowed).
Forgetting NOT NULL when adding a composite key with ALTER TABLE; databases require each column to be NOT NULL.
Assuming a primary key is the same as a UNIQUE constraint; UNIQUE allows multiple nulls, whereas a primary key does not allow nulls at all.
Dropping a primary key without first considering dependent foreign keys in child tables.

First Introduced In

SQL-92

Frequently Asked Questions

What happens if I try to insert a duplicate primary key value?

The database rejects the insert or update and raises a unique violation error.

Can I change the primary key of a table later?

Yes. Drop the existing primary key with ALTER TABLE ... DROP CONSTRAINT, then add a new one. Be sure to update any foreign keys.

Does a primary key automatically create an index?

In all major databases the primary key implicitly creates a unique index, improving query performance on the key columns.

Is it mandatory to declare NOT NULL on primary key columns?

Not explicitly. Creating a primary key implicitly sets each key column to NOT NULL, but you can also specify it yourself for clarity.