I have this table with a column called wo_number, which is basically an auto-generated string value from a timestamp column. This kind of gives a unique value (since it take into account seconds and my application doesn't allow multiple inserts); the thing is, I want to scale for more than one insert in a second. Therefore, I want to make it 100% unique by auto-increment if two inserts came at the same time.
the wo_number column value looks as follow: WO-071821145515
I want it to look like this : WO-071821145515 - 1 <<< where 1 is an auto-increment in case there is a duplication.
my table query is:
[create table work_orders (
id int auto_increment PRIMARY KEY,
time_stamp TIMESTAMP DEFAULT now(),
descreption text,
requested_by varchar(100),
urgent BOOLEAN,
assigned_to varchar(100),
completion_date TIMESTAMP -- DEFAULT now(),
ack_date timestamp -- DEFAULT now(),
wo_number varchar(100) as (concat('WO-',replace(DATE_FORMAT(time_stamp,"%m%d%y%T"),':',''))),
remarks text,
machine_id int,
department_id int,
employee_id int,
foreign key (machine_id) REFERENCES machines(id),
foreign key (department_id) REFERENCES departments(id),
foreign key (employee_id) REFERENCES employees(id)
);
