:Add a primary key to a table that does not have a primary key in an ApsaraDB RDS for MySQL instance

Query tables that do not have primary keys

select table_schema,table_name from information_schema.tables
where (table_schema,table_name) not in(
    select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'
)
and table_schema not in (
    'sys','mysql','information_schema','performance_schema'
);

Add a primary key to a table that does not have a primary key

• Add an implicit primary key
  1. Execute the following statement to check whether the implicit_primary_key parameter is set to ON:

     show global variables  like 'implicit_primary_key';

  2. Execute the following statement to modify the table that does not have a primary key:

     alter table <Table name> engine=innodb;

     Note

     • When you execute the preceding statements, you cannot write data to the table. However, you can read data from the table.
     • If you set the implicit_primary_key parameter to ON, the system automatically adds an implicit primary key to the table when you modify the engine of the table.
• Add a primary key
  Execute the following statement to add a primary key to a table that does not have a primary key:

  ALTER TABLE <Table name> ADD PRIMARY KEY (<Name of the column that you want to specify as the primary key>);