MySQL Primary Key check

You should check all tables of type InnoDB and MyISAM that there is a primary or unique key. If there is no key do the following :

ALTER TABLE user1.table1 ADD id INT PRIMARY KEY AUTO_INCREMENT;

 

To find out the tables without any unique key do the following:

select
    t.table_schema,t.table_name,t.engine
from 
    information_schema.tables t
    inner join information_schema.columns c
        on t.table_schema=c.table_schema and t.table_name=c.table_name
where t.engine='MyISAM' or t.engine='InnoDB'
group by
    t.table_schema,t.table_name  
having
    sum(if(column_key in ('PRI','UNI'), 1,0)) =0;

 

We use cookies

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.