MySQL Primary Keys

In a relational database, a primary key is a field or combination of fields that uniquely identifies each row in a table.

MySQL primary key rules

In MySQL, primary keys need to follow these rules:

The primary key is defined on the table. A table is not mandatory to define a primary key, but can only define at most one primary key.
The primary key can contain one column or multiple columns.
Primary key column values must be unique. If the primary key contains multiple columns, the values of these columns must combine to be unique.
Primary key columns cannot contain NULL values.

If the above rules are not followed, the following errors may be raised.

If multiple primary keys are defined, an error is returned: ERROR 1068 (42000): Multiple primary key defined.
If there are duplicate primary key values when inserting or updating, it causes an error like: ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'.
A similar error is returned if you write a NULL value to the primary key column: ERROR 1048 (23000): Column 'id' cannot be null.

How to define a primary key?

You can define the primary key while creating the table, as follows:

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(45)
);

This defines the id column is the primary key.

The above method is suitable for the case where only one column is used as the primary key. If the primary key contains multiple columns, please use the following method:

CREATE TABLE user (
  com_id INT,
  user_number INT,
  name VARCHAR(45),
  PRIMARY KEY(com_id, user_number)
);

This defines a primary key that contains 2 columns: com_id and user_number.

How to add a primary key?

If you did not set the primary key when you created the table, and now you want to add a primary key, please use the following ALTER TABLE statement:

ALTER TABLE user
ADD PRIMARY KEY(id);

Here a primary key is added to the user table, which includes id the column.

Note that when you add a primary key to a table with data, since the primary key requires the column’s value to be unique and cannot be NULL, if there are duplicate values or NULL values in the column, an error similar to the following will be returned:

ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'

How to drop a primary key?

If you want to drop the primary key on a table, please use the following ALTER TABLE statement:

ALTER TABLE user
DROP PRIMARY KEY;

Here, you removed the primary key on the user table.

How to generate primary key value

Usually, in business systems, we do not use business fields as primary keys, although they are also unique. We generally use a separate field as the primary key, mainly for the following two reasons:

Protect the business data
It is convenient to modify these business fields

In order to generate a unique primary key value, we usually use the following methods:

Set the primary key column to AUTO_INCREMENT.

The columns declared AUTO_INCREMENT as automatically generate consecutive integer values.
Use the UUID() function to generate unique value.

The UUID() function produces a string of length 36 characters and never repeats, as follows:
```
SELECT UUID();
```
```
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 523efe70-29ac-11ec-a78b-6dba5fac4247 |
+--------------------------------------+
1 row in set (0.00 sec)
```
UUID() is suitable for use in a cluster environment. In this way, even if a table is partitioned on multiple servers, records with the same primary key will not be generated.

Use the UUID_SHORT() function to generate unique value.

The UUID_SHORT() function returns a 64-bit unsigned integer and is globally unique. as follows:

  SELECT UUID_SHORT();

+-------------------+
| UUID_SHORT()      |
+-------------------+
| 99469056502923283 |
+-------------------+
1 row in set (0.00 sec)

Primary key vs unique Key/Index

Both primary keys and unique indexes require values to be unique, but there are some differences between them:

Only one primary key can be defined in a table, but multiple unique indexes can be defined.
The value in the primary key cannot be NULL, while the value in the index can be NULL.

Conclusion

In this article, we learned what primary keys are, the rules of primary keys, and how to use primary keys in MySQL. The main points of this article are as follows:

The primary key is defined on the table. A table can only define at most one primary key.
The primary key can contain one column or multiple columns.
Primary key column values must be unique. If the primary key contains multiple columns, the values of these columns must combine to be unique.
Primary key columns cannot contain NULL values.