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.
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:
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
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) );