CODEWITHSUNDEEP
mysqlsql
chiefo
chiefo

Reputation: 281

How to add Auto-Increment to all database Tables with Primary key set

I use

ALTER TABLE `tablename` DROP PRIMARY KEY;
ALTER TABLE `tablename` MODIFY `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

To add Auto increment to a specific table with column name ID, However, what I want is to:

1 - Add Auto-Increment to all tables in a particular database (even if there are 1000 tables) with column name ID.

2 - Check if primary key is present in each table name with column name ID to be able to alter it.

The reason is I have a database with over 2,000 tables and after upgrading my xammp version, it seems to remove the auto-increment but retains primary key value.

Upvotes: 0

Views: 1111

Answers (1)

blabla_bingo
blabla_bingo

Reputation: 2162

We can use a cursor to loop through all tables which has an ID column in a specific database. Then use prepared statement to execute necessary alter table statements. Everything is done in a procedure . It's written and tested in workbench. Please note, based on your illustration, this is assuming all your tables' PK in the database are on column 'ID',which means NO OTHER columns are PK. Additionally, NO OTHER columns are auto_increment.

create database testdatabase;
use testdatabase;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (id int,num int);
create table t2 (id int primary key, num int);
create table t3 (id int primary key auto_increment, num int);

delimiter //
drop procedure if exists add_pk_ai//
create procedure add_pk_ai(db_name varchar(20),col_name varchar(20),dt_type varchar(20)) -- in parameters include the database name, column name ,and the data type of the column
begin
declare tb_name varchar(20);
declare col_key varchar(20);
declare col_extra varchar(20);
declare done bool default false;
declare c cursor for select table_name,column_key,extra 
from information_schema.columns 
where table_schema=db_name and column_name=col_name and data_type=dt_type;
declare continue handler for not found set done=true;

open c;
lp:loop
fetch c into tb_name,col_key,col_extra;
if done=true then
leave lp;
end if;

if col_key!='PRI'  then
set @stmt=concat('alter table ',db_name,'.',tb_name,' add primary key (',col_name,');');
prepare stmt1 from @stmt;
execute stmt1;
deallocate prepare stmt1;
end if;

if col_extra!='auto_increment' then
set @stmt=concat('alter table ',db_name,'.',tb_name,' modify ',col_name,' ',dt_type,' auto_increment ;');
prepare stmt2 from @stmt;
execute stmt2;
deallocate prepare stmt2;
end if;

end loop lp;

end// 
delimiter ;
-- let's test the procedure
call add_pk_ai('testdatabase','id','int'); 

desc t1;
-- result set:
# Field, Type, Null, Key, Default, Extra
id, int(11), NO, PRI, , auto_increment
num, int(11), YES, , , 

desc t2;
-- result set:
# Field, Type, Null, Key, Default, Extra
id, int(11), NO, PRI, , auto_increment
num, int(11), YES, , , 

desc t3;
-- result set:
# Field, Type, Null, Key, Default, Extra
id, int(11), NO, PRI, , auto_increment
num, int(11), YES, , ,

Upvotes: 2

Related Questions