Archive for October, 2024

Find tables without primary keys

Some plugins create tables in MYSQL without primary keys which may break exporting and importing actions.

We can identify where exactly with this SQL script:

SELECT
t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
ON (
t.TABLE_NAME = k.TABLE_NAME
AND k.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
AND k.constraint_name = 'PRIMARY'
)
WHERE
t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND t.TABLE_SCHEMA = 'database name' -- put database name here
AND k.constraint_name IS NULL;