MariaDB: Primary Keys

This MariaDB tutorial explains how to create and drop a primary key in MariaDB with syntax and examples.

What is a primary key in MariaDB?

In MariaDB, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a NULL value. A table can have only one primary key.

A primary key is created in MariaDB using either a CREATE TABLE statement or an ALTER TABLE statement.

Create Primary Key - Using CREATE TABLE statement

You can create a primary key in MariaDB with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in MariaDB is:

CREATE TABLE table_name
(
  column1 column_definition,
  column2 column_definition,
  ...

  CONSTRAINT [constraint_name] 
   PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table. See the MariaDB CREATE TABLE statement for more detailed CREATE TABLE syntax as this is an over-simplification to demonstrate how to create a Primary Key.

constraint_name

The name of the primary key.

column1, column2, ... column_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in MariaDB.

CREATE TABLE websites
( website_id INT(11) NOT NULL AUTO_INCREMENT,
  website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_id)
);

In this example, we've created a primary key on the websites table called websites_pk. It consists of only one column - the website_id column.

We could also create a primary key with more than one field as in the example below:

CREATE TABLE websites
( website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_name, server_name)
);

This example creates a primary key called websites_pk that is made up of a combination of the website_name and server_name columns. So each combination of website_name and server_name must be unique in the websites table.

Create Primary Key - Using ALTER TABLE statement

You can create a primary key in MariaDB with the ALTER TABLE statement.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in MariaDB is:

ALTER TABLE table_name
  ADD CONSTRAINT [ constraint_name ]
    PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)

table_name

The name of the table to modify.

constraint_name

The name of the primary key.

column1, column2, ... column_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in MariaDB.

ALTER TABLE websites
  ADD CONSTRAINT websites_pk
    PRIMARY KEY (website_id);

In this example, we've created a primary key on the existing websites table called websites_pk. It consists of the website_id column.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE websites
  ADD CONSTRAINT websites_pk
    PRIMARY KEY (website_name, server_name);

This example we've created a primary key called websites_pk that is made up of a combination of the website_name and server_name columns.

Drop Primary Key

You can drop a primary key in MariaDB using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key in MariaDB is:

ALTER TABLE table_name
  DROP PRIMARY KEY;

table_name

The name of the table to modify.

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in MariaDB.

ALTER TABLE websites
  DROP PRIMARY KEY;

In this example, we've dropped the primary key on the websites table. We do not need to specify the name of the primary key as there can only be one on a table.