MySQL: Primary Keys

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

What is a primary key in MySQL?

In MySQL, 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.

Note

• In MySQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement.
• You use the ALTER TABLE statement in MySQL to drop, disable or enable a primary key.

Create Primary Key - Using CREATE TABLE statement

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

Syntax

The syntax to create a primary key using the CREATE TABLE statement in MySQL 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 MySQL 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 MySQL.

CREATE TABLE contacts

( contact_id INT(11) NOT NULL AUTO_INCREMENT,

  last_name VARCHAR(30) NOT NULL,

  first_name VARCHAR(25),

  birthday DATE,

  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)

);

In this example, we've created a primary key on the contacts table called contacts_pk. It consists of only one column - the contact_id column.

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

CREATE TABLE contacts

( last_name VARCHAR(30) NOT NULL,

  first_name VARCHAR(25) NOT NULL,

  birthday DATE,

  CONSTRAINT contacts_pk PRIMARY KEY (last_name, first_name)

);

This example creates a primary key called contacts_pk that is made up of a combination of the last_name and first_name columns. So each combination of last_name and first_name must be unique in the contacts table.

Create Primary Key - Using ALTER TABLE statement

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

Syntax

The syntax to create a primary key using the ALTER TABLE statement in MySQL 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 MySQL.

ALTER TABLE contacts

  ADD CONSTRAINT contacts_pk

    PRIMARY KEY (contact_id);

In this example, we've created a primary key on the existing contacts table called contacts_pk. It consists of the contact_id column.

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

ALTER TABLE contacts

  ADD CONSTRAINT contacts_pk

    PRIMARY KEY (last_name, first_name);

This example we've created a primary key called contacts_pk that is made up of a combination of the last_name and first_name columns.

Drop Primary Key

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

Syntax

The syntax to drop a primary key in MySQL 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 MySQL.

ALTER TABLE contacts

  DROP PRIMARY KEY;

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