MySQL – Is Setting Trigger BEFORE INSERT for New ID Safe?

auto-incrementMySQL

How to create customize primary key in MySQL?, example i have table and the table name is X, I have a table field as ID,Code,Name.

I am afraid if I have 1000 users and when they input together will
result in destruction

and i want to :

INSERT INTO `X` (`ID`,`Code`,`Name`) VALUES 
('P3K','Alex'), // this primary key is "P3K-1"
('SOS','Force'), // this primary key is "SOS-1"
('P3K','Bash'), // this primary key is "P3K-2"

Right now, i using TRIGGER (BEFORE INSERT) for this, like this one:

SET NEW.`ID` = CONCAT(NEW.`Code`,'-',IFNULL(SUBSTRING_INDEX((
  SELECT `x`.`Code` FROM `X` WHERE 
    X.`Code` = NEW.`Code` and 
ORDER BY X.`Code` DESC
LIMIT 1 ),'-',-1),0) + 1))

I did not try this code, but my point is:

  1. User insert
  2. Before insert I checking LAST Primary
  3. IF Null then i set 0, else i cut the symbol (-) and take the last part
  4. I increments (using [+ 1])
  5. Final, i concat CODE and New Number.

am i misguided? LOL, and if true, how to create like this one?
(I THINK) We can do it and maybe no one knows about this, how does AI in MySQL work so perfectly?

Best Answer

Do not make auto_increment any column you want to manipulate explicitly. That can confuse an engine and cause serious problems. If no column you have used for primary key are auto_increment you can do anything you want with them via triggers. Sure generated values will be rejected if they violate the mandatory uniqness of the primary key.

Related Question