Create missing primary keys#

Primary keys are important for MySQL replication process. In this article, you can find strategies to create missing primary keys in your Aiven for MySQL service.

List tables without primary key#

Once you are connected to the MySQL database, you can determine which tables are missing primary keys by running the following commands:

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 problematic tables you can do:

SHOW CREATE TABLE database_name.table_name;

If your table already contains a column or set of columns that can be used as primary key or composite key, then using such column(s) is recommended. In the next articles, find some examples of tables definitions, and how to create the missing primary keys.

Example: add primary key#

CREATE TABLE person (
social_security_number VARCHAR(30) NOT NULL,
first_name TEXT,
last_name TEXT
);

You can create the missing primary key by first adding the primary key:

ALTER TABLE person ADD PRIMARY KEY (social_security_number);

You don’t have to explicitly define it as UNIQUE, as primary key is always unique in MySQL.

Example: add a new separate id column#

CREATE TABLE team_membership (
user_id BIGINT NOT NULL,
team_id BIGINT NOT NULL
);

Same as before, add the primary key by following:

ALTER TABLE team_membership ADD PRIMARY KEY (user_id, team_id);

If none of the existing columns or a combination of the existing columns can not be used as the primary key, then you should add a new separate id column. See how to deal with it in the Alter table error example.

ALTER TABLE mytable ADD id BIGINT PRIMARY KEY AUTO_INCREMENT;

Example: alter table error innodb_online_alter_log_max_size#

When executing the ALTER TABLE statement for a large table, you may encounter an error like this:

Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

You will need to set a high enough value for the operation to succeed. Depending on the table size this could be a few gigabytes or even more for very large tables. You can change the innodb_online_alter_log_max_size by selecting your Aiven for MySQL service and go to Overview > Advanced configuration > Change > Add configuration option to add the parameter and make changes.

See also

Learn how to create new tables without primary keys in your Aiven for MySQL.