# 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](https://docs.digitalocean.com/reference/api/digitalocean/index.html.md#operation/databases_patch_config) 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:

```shell
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:

```mysql
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:

```mysql
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`.

```mysql
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:

```mysql
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.

```mysql
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](https://dev.mysql.com/doc/workbench/en/wb-table-editor-columns-tab.html) to define primary keys for your tables