Create missing primary keys
Learn strategies to create missing primary keys in your Aiven for MySQL® service. They are important for MySQL replication process.
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 run the following command:
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 columns is recommended.
In the next sections, find examples of tables definitions and the guidance on 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 adding the primary key:
ALTER TABLE person ADD PRIMARY KEY (social_security_number);
You don't have to explicitly define it as UNIQUE, as the primary key is always unique in MySQL.