How to Create Primary Keys for MySQL Database Tables

MySQL is an open source, object-relational database built with speed and reliability in mind. Its large and active developer community has created many third-party applications, tools, and libraries that expand MySQL’s functionality.

Primary keys are a single field or combination of fields that are defined to become a unique identifier for a row in a table, such as a row’s number or a name field combined with a birth date field. Primary keys increase search accuracy and performance, and they enhance cross-reference relationships between tables. A table can have only one primary key, and a primary key field cannot contain a null value.

Note

By default, primary keys are a requirement in all MySQL database tables. This ensures reliable data replication. However, you can turn off a database’s primary key requirement by making a configuration request via our API.

How DigitalOcean Uses Primary Keys

We use replication to communicate between cluster nodes to ensure that primary, standby, and read-only nodes remain in sync. In addition, we apply a full replication stream anytime you create a new node based on an existing backup. The service’s following features depend on replication:

• Restoring a service from backup
• Automatically recovering from a node failure
• Forking a new service from an existing service
• Migrating an existing service to a new datacenter region
• Scaling an existing service to a larger size
• Adding standby or read-only nodes to an existing service

How to Manually Add a Primary Key

To determine which tables are missing primary keys, copy and paste the following command into the MySQL shell and run it:

SELECT
    tab.table_schema AS database_name,
    tab.table_name AS table_name,
    tab.table_rows AS table_rows
FROM information_schema.tables tab
LEFT JOIN information_schema.table_constraints tco
    ON (tab.table_schema = tco.table_schema
        AND tab.table_name = tco.table_name
        AND tco.constraint_type = 'PRIMARY KEY')
WHERE
    tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
    AND tco.constraint_type IS NULL
    AND tab.table_type = 'BASE TABLE';

To see the exact table definition for the tables, use the SHOW statement, replacing the placeholder values with your own:

SHOW CREATE TABLE your_database_name.your_table_name;

To add a primary key to an existing table, use the ALTER TABLE statement, replacing the placeholder values with your own:

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

For example, the command below combines a customer ID column with a customer last name column to create a primary key in a table named car.

ALTER TABLE car ADD PRIMARY KEY (customer_id,LastName);

To add a primary key for a new MySQL table, use the CREATE TABLE statement, replacing the placeholder values with your own:

CREATE TABLE your_table_name
(
  column1 <column_definition>,
  column2 <column_definition>,
  ...
  PRIMARY KEY (customer_id,LastName)
);

For example, the command below creates a table that combines the customer ID column with the customer name column to create a primary key.

CREATE TABLE car (
    customer_id VARCHAR(20) NOT NULL,
    FirstName varchar(255),
    LastName varchar(255),
    PRIMARY KEY (customer_id,LastName)
);

Other Tools and Resources

• Use MySQL Workbench to define primary keys for your tables