1

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.