MariaDB Primary Key

Summary: in this tutorial, you will learn how to define the primary key for a table by using the MariaDB primary key constraint.

What is a primary key

A primary key is a column or group of columns whose values uniquely identify every row in the table.

A table can have zero or one primary key. Even though a primary key is optional, it is a good practice to define a primary key for every table.

Defining a primary key

The primary key constraint allows you to define the primary key for a table. Typically, you define a primary key for a table during table creation.

This statement shows the syntax for creating a table with a primary key:

create table table_name(
    pk_column type primary key,
    ...
);
Code language: SQL (Structured Query Language) (sql)

If the primary key consists of two or more columns, you use the primary key table constraint instead as shown in the following statement:

create table table_name(
    pk_column1 type,
    pk_column2 type,
    ...
    primary key(pk_column1,pk_column2,)
);
Code language: SQL (Structured Query Language) (sql)

When you create a table with a primary key, MariaDB automatically creates a btree index called primary that includes the primary key column or columns.

By default, primary key columns are implicitly imposed not null constraints, therefore, you don’t need to explicitly specify the not null constraints for the primary key columns.

Adding a primary key to a table

To add a primary key to an existing table, you use the alter table statement:

alter table table_name
Code language: SQL (Structured Query Language) (sql)

[add constraint constraint_name]

primary key (column_list);

In this syntax:

First, specify the name of the table to which you want to add a primary key.
Then, specify the name of the primary key constraint followed by the primary key keywords with a list of comma-separated primary key columns within parentheses.

Note that the add constraint clause is optional.

Dropping a primary key from a table

To delete a primary key from a table, you use the following form of the alter table statement:

alter table table_name
drop primary key;
Code language: SQL (Structured Query Language) (sql)

In practice, you will rarely drop a primary key.

Primary key and auto_increment column

Typically, you will use an auto_increment column as the primary key column. The following shows a general syntax for creating a table with an auto_increment column as the primary key column:

create table table_name(
    pk_column int auto_increment primary key,
    ...
);
Code language: SQL (Structured Query Language) (sql)

create table table_name(
    pk_column int auto_increment,
    ...,
    primary key(pk_column)
);
Code language: SQL (Structured Query Language) (sql)

MariaDB primary key examples

Let’s take some examples of defining primary keys.

A) Creating a table with a primary key example

Suppose that a product code uniquely identifies a product. The following statement creates a table called products whose primary key is the product code column:

create table if not exists products(
    product_code varchar(18) primary key,
    product_name varchar(50) not null
);
Code language: SQL (Structured Query Language) (sql)

The following statement inserts a new product into the products table:

insert into products(product_code, product_name)
values('xg-2019','xgadget of 2019');
Code language: SQL (Structured Query Language) (sql)

The following statement attempts to insert a new product whose product code already exists:

insert into products(product_code, product_name)
values('xg-2019','xgadget of 2019-q2');
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following error:

SQL Error (1062): Duplicate entry 'xg-2019' for key 'PRIMARY'
Code language: SQL (Structured Query Language) (sql)

To view the index associated with the primary key, you use the show indexes statement:

show indexes from products;
Code language: SQL (Structured Query Language) (sql)

B) Creating a table with the primary key as an auto_increment column

The following example creates a new table called categories whose primary key column is an auto_increment column:

create table categories(
    category_id int auto_increment,
    name varchar(50) not null,
    primary key(category_id)
);
Code language: SQL (Structured Query Language) (sql)

If you insert a new row into the categories table without specifying a value for the category_id column, MariaDB will automatically generate a sequential integer for it.

insert into 
    categories(name)
values
    ('Gadgets'),
    ('Accessories');
Code language: SQL (Structured Query Language) (sql)

Here are the contents of the categories table:

select * from categories;
Code language: SQL (Structured Query Language) (sql)

C) Creating a new table whose primary key has multiple columns

The following statement creates a new table whose primary key consists of two columns:

create table product_categories(
    product_code varchar(18),
    category_id int,
    primary key(product_code,category_id)
);
Code language: SQL (Structured Query Language) (sql)

The product_categories table stores the relationship between tables products and categories. Each product may belong to zero or more categories. On the other hand, each category can have zero or more products. This relationship is called the many-to-many.

Note that you will learn how to enforce the referential integrity between the tables products, categories, and product_categories by using the foreign key constraints in the subsequent tutorial.

In this tutorial, you learned how to use the MariaDB primary key constraints to define primary keys for tables.

Was this tutorial helpful ?