Finding Tables without Primary Key

Having a primary key defined for each user table is best practice for performance in InnoDB. And when using Group Replication or InnoDB Cluster for automatic high availability it is (almost) mandatory. (The full story see here.) So it is wise to check if you have tables running without primary key. You can identify these tables by running:

SELECT t.table_schema, t.table_name FROM tables AS t 

       LEFT JOIN key_column_usage AS c  

       ON (t.table_name = c.table_name AND 

           c.constraint_schema = t.table_schema AND 

           c.constraint_name = 'PRIMARY' ) 

   WHERE t.table_schema NOT IN ("mysql", "information_schema", 

                                "performance_schema", "sys") 

         AND c.constraint_name IS NULL

         AND t.table_type = "BASE TABLE"; 

And if you want to make life even easier, you can add this as a report to the sys schema:

CREATE VIEW sys.schema_tables_without_pk AS

SELECT t.table_schema, t.table_name FROM tables AS t 

       LEFT JOIN key_column_usage AS c  

       ON (t.table_name = c.table_name AND 

           c.constraint_schema = t.table_schema AND 

           c.constraint_name = 'PRIMARY' ) 

   WHERE t.table_schema NOT IN ("mysql", "information_schema", 

                                "performance_schema", "sys") 

         AND c.constraint_name IS NULL

         AND t.table_type = "BASE TABLE"; 

It is easy to detect but a little more challenging to solve. You need to consider your application and potential load when adding a new primary key. One solution is to add a new auto_increment column. In many cases this might help already. In other cases you already have a natural primary key in your table definition, It's just not defined as such.

To add auto_increment columns to all affected tables (which I do not recommend without thinking about it and testing first!), you can use the beauty of the Python mode in MySQL Shell:

$ mysqlsh root@localhost:33060 --py

MySQL Shell 8.0.16

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.

Creating a session to 'root@localhost:39010'

Fetching schema names for autocompletion... Press ^C to stop.

Your MySQL connection id is 762 (X protocol)

Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial

No default schema selected; type \use <schema> to set one.

MySQL  localhost:33060+ ssl  Py >l=session.get_schema("sys").get_table("schema_tables_without_pk").select().execute().fetch_all()

MySQL  localhost:33060+ ssl  Py >for val in l: session.sql("ALTER TABLE "+val[0]+"."+val[1]+" ADD COLUMN (__id int unsigned auto_increment PRIMARY KEY)");