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.

We require the definition of a primary key on each table in every new MySQL database created after 26 May 2020. For all tables created before 8 April 2020 we recommend defining a primary key to ensure reliable data replication.

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 data center 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:

    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')
    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.

    customer_id VARCHAR(20) NOT NULL,
    FirstName varchar(255),
    LastName varchar(255),
    PRIMARY KEY (customer_id,LastName)

Other Tools and Resources