MySQL Primary Key

Summary: in this tutorial, you will learn how to use the MySQL primary key constraint to create the primary key for a table.

Introduction to the MySQL primary key

In MySQL, a primary key is a column or a set of columns that uniquely identifies each row in the table. A primary key column must contain unique values.

If the primary key consists of multiple columns, the combination of values in these columns must be unique. Additionally, a primary key column cannot contain NULL.

A table can have either zero or one primary key, but not more than one.

Defining a single-column primary key

Typically, you define a primary key for a table when you create the table. Here’s the syntax for defining the primary key that consists of one column:

CREATE TABLE table_name(
   column1 datatype PRIMARY KEY,
   column2 datatype, 
   ...
);

In this syntax, you define the PRIMARY KEY constraint as a column constraint.

Additionally, you can put the PRIMARY KEY at the end of the column list:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype, 
   ...,
   PRIMARY KEY(column1)
);

In this syntax, you define the PRIMARY KEY constraint as a table constraint.

Defining a multi-column primary key

If the primary key consists of two or more columns, you need to use a table constraint to define the primary key:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ...,
   PRIMARY KEY(column1, column2)
);

In this syntax, you list the primary key columns inside parentheses, separated by commas, followed by the PRIMARY KEY keywords.

Adding a primary key to an existing table

If an existing table does not have a primary key, you can add a primary key to the table using the ALTER TABLE ... ADD PRIMARY KEY statement:

ALTER TABLE table_name
ADD PRIMARY KEY(column1, column2, ...);

Removing a primary key

In practice, you’ll rarely remove a primary key. However, if you want to do so, you can use the ALTER TABLE ... DROP PRIMARY KEY statement:

ALTER TABLE table_name
DROP PRIMARY KEY;

MySQL PRIMARY KEY examples

We’ll explore some examples of defining primary keys.

1) Defining a single-column primary key example

The following example creates a table called products, which has the id column as the primary key:

CREATE TABLE products(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL
);Code language: PHP (php)

When you insert data into the products table, you need to ensure the uniqueness of values in the id column. For example:

INSERT INTO products (id, name) 
VALUES 
    (1, 'Laptop'),
    (2, 'Smartphone'),
    (3, 'Wireless Headphones');Code language: JavaScript (javascript)

If you attempt to insert a duplicate value into the primary key column, you’ll get an error. For example:

INSERT INTO products (id, name) 
VALUES 
  (1, 'Bluetooth Speaker');Code language: JavaScript (javascript)

Error:

ERROR 1062 (23000): Duplicate entry '1' for key 'products.PRIMARY'Code language: JavaScript (javascript)

The output indicates that MySQL found a duplicate entry 1 for the primary key of the products table.

Keeping track of primary key values manually can be challenging. To simplify this process, MySQL provides the AUTO_INCREMENT attribute, which automatically assigns a unique value to the primary key each time you insert a new record.

2) Defining a single-column primary key with AUTO_INCREMENT attribute example

The following statements re-create the products table with the primary key that uses the AUTO_INCREMENT attribute:

DROP TABLE products; 

CREATE TABLE products(
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255) NOT NULL
);Code language: PHP (php)

Now, you can insert new rows into the products table without having to provide the values for the primary key column. For example:

INSERT INTO products (name) 
VALUES 
    ('Laptop'),
    ('Smartphone'),
    ('Wireless Headphones');Code language: JavaScript (javascript)

MySQL automatically generates sequential integer values for the id column when a new row is inserted.

Here’s the contents of the products table:

SELECT * FROM products;

Output:

+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Laptop              |
|  2 | Smartphone          |
|  3 | Wireless Headphones |
+----+---------------------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

3) Defining a multi-column primary key example

We’ll create a new table called customers:

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);Code language: PHP (php)

Suppose each customer has some favorite products and each product is favored by some customers.

To model this relationship, you need to create a table called favorites:

CREATE TABLE faviorites(
    customer_id INT,
    product_id INT,
    favorite_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(customer_id, product_id)
);Code language: PHP (php)

The favorites table has a primary that consists of two columns customer_id and product_id.

Note that in the foreign key tutorial, you’ll learn how to define a foreign key for the customer_id column that references the id column of the customers table and a foreign key for the product_id column that references the id column of the products table.

4) Adding a primary key to a table example

The following statement creates a table called tags without a primary key:

CREATE TABLE tags(
    id INT,
    name VARCHAR(25) NOT NULL
);Code language: PHP (php)

To make the id column the primary key, you use the ALTER TABLE ... ADD PRIMARY KEY statement:

ALTER TABLE tags
ADD PRIMARY KEY(id);

5) Removing the primary key from a table

The following statement removes the primary key from the tags table:

ALTER TABLE tags
DROP PRIMARY KEY;

Summary

A primary key is a unique identifier for a row in a table.
Use the PRIMARY KEY constraint to define a primary key for a table.

Was this tutorial helpful?