code smell - select max (numberProntuario) +1, but how to fix it?

Navigation
0

I have a Paciente mysql table, in it I have the column id , nome , numeroProntuario etc. This column numeroProntuario is defined by the increment of +1 resulting from select max(numeroProntuario) (legacy code, do not judge me). Now, I'm implementing a waiting list, and I'm integrating with this table, noticing this problem, I thought of using the same id number in the chart number. But I have not found anything like it and I still can not change the previous records.

Does anyone have any idea how I can fix this, or is it not fixed, and should I play anyway?

Patient Table: 

id      numeroProntuario    nome
5565    5548                ANA
5566    5549                PRISCILA
5567    5550                MIRIAN 
5568    5551                ALEXANDRE
5569    5552                JACONIAS 
5570    5553                KASSIO 
5571    5554                KEROLY 
5572    5555                ALEX
    
asked by anonymous 24.09.2018 / 21:51

1 answer

1

Hello, Validate this solution: Change the column to auto_increment and set the current value of it: 

ALTER TABLE Paciente MODIFY COLUMN numeroProntuario INT auto_increment;
ALTER TABLE Paciente AUTO_INCREMENT = select max(numeroProntuario)+1 from Paciente;

UPDATE:

As the table can be only 1 auto_increment column, the value increment can be done via trigger and the select removed from the application: 

CREATE TRIGGER SetNumeroProntuario BEFORE INSERT ON Paciente 
FOR EACH ROW BEGIN
  SET NEW.numeroProntuario = select max(numeroProntuario)+1 from Paciente;
END
    
24.09.2018 / 22:03
Insert variable in script Import VueJs template from an external html file