CODEWITHSUNDEEP
mysqlauto-increment
Vidyadhar Mandke
Vidyadhar Mandke

Reputation: 588

Mysql table with more than one Auto-Incremented column

I have a table with the following structure:

 id |  number |  text
----------------------
  1 |    1    |  test

in which, id is my primary key with auto increment value. I want to make number as auto increment value too. Is it possible to have more than one auto increment column in one table?

Upvotes: 3

Views: 11068

Answers (2)

Toretto
Toretto

Reputation: 4711

It is not possible.There can be only one auto-increment column and it must be defined as a key in MySQL. But You can do it by using trigger for detail go this link CREATE TRIGGER

Upvotes: 10

phoniq
phoniq

Reputation: 238

create trigger nameTrigger before insert on tables
for each row
begin
  DECLARE newNumber unsigned default 0;
  SELECT Max(number)+1 INTO newNumber FROM myTable WHERE id = new.id;
  UPDATE myTable SET number = newNumber WHERE id = new.id;
end

Upvotes: 4

Related Questions