How to Add a Primary Key in MariaDB

Galaxy Glossary

How do I add a primary key to an existing MariaDB table?

ADD PRIMARY KEY creates or enforces a unique, non-NULL identifier on one or more columns in a MariaDB table.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why add a primary key to an existing table?

Primary keys guarantee row uniqueness, speed up lookups, and enable efficient joins. Adding one later helps legacy tables gain these advantages without rebuilding data.

What is the syntax for ALTER TABLE ADD PRIMARY KEY?

Use ALTER TABLE followed by ADD PRIMARY KEY and a comma-separated list of columns in parentheses. You may optionally name the constraint.

How do I add a single-column primary key?

Supply one column after ADD PRIMARY KEY. The column must already be NOT NULL or the statement will fail.

How do I add a composite primary key?

List multiple columns inside the parentheses. MariaDB treats the combined values as the unique identifier.

Can I add a primary key and modify column types in one statement?

Yes. Place MODIFY or CHANGE clauses before ADD PRIMARY KEY within the same ALTER TABLE. This prevents extra table rebuilds.

Does adding a primary key lock the table?

ALTER TABLE is generally blocking. On large tables, use pt-online-schema-change or ALTER ONLINE if available to avoid downtime.

Why How to Add a Primary Key in MariaDB is important

How to Add a Primary Key in MariaDB Example Usage


-- Add a primary key to Orders when id was missing
ALTER TABLE Orders
    ADD PRIMARY KEY (id);

-- If id is NULL-able, fix it first
UPDATE Orders SET id = ROW_NUMBER() OVER () WHERE id IS NULL;
ALTER TABLE Orders
    MODIFY id INT NOT NULL,
    ADD PRIMARY KEY (id);

How to Add a Primary Key in MariaDB Syntax


ALTER TABLE table_name
    ADD [CONSTRAINT constraint_name]
    PRIMARY KEY (column1 [, column2, ...]);

-- Ecommerce examples
-- 1. Add single-column PK to Customers
ALTER TABLE Customers
    ADD PRIMARY KEY (id);

-- 2. Add composite PK to OrderItems
ALTER TABLE OrderItems
    ADD CONSTRAINT pk_orderitems
    PRIMARY KEY (order_id, product_id);

Common Mistakes

Omitting NOT NULL on primary key columns. MariaDB will reject the statement. Fix by altering the column to NOT NULL before adding the key.
Creating duplicate primary keys. A table can have only one primary key. Drop the existing key with ALTER TABLE DROP PRIMARY KEY before adding a new one.

Frequently Asked Questions (FAQs)

Can I add a primary key that spans more than one column?

Yes—list multiple columns inside the parentheses after ADD PRIMARY KEY. The combined values must be unique and non-NULL.

Will adding a primary key automatically create an index?

MariaDB automatically creates a unique index backing the primary key, improving query performance on the specified columns.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie