Hello friends!!! In today’s post we will work with autonumerics in Oracle, MySQL and SQL Server. We will see how to get the last value of the fields IDENTITY (SQL SERVER), AUTO_INCREMENT (MySQL) and IDENTITY (ORACLE from version 12c. Sequence in previous versions).
If you have in mind to make a select MAX (…) of the field to consult… DON’T DO IT! Not a good idea. The problem is that if you try in your test team it will work well for you, but in reality it will not work for you.
Why is it not reliable to get the autonumerics with Select MAX?
The reason is that in environments with many simultaneous users, between you entering your order in the table and making the query to see the maximum ID, another user may have entered another order in the same table that will already have a higher ID. If you simply find out the maximum you run the risk (almost certainly) that your order lines and your customer will be associated with an order that does not correspond to them. Do you see the problem?
Let’s see how to get the autonumeric values in Oracle, MySQL and SQL Server
Returns the last identity value inserted into a identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. therefore if two instructions are in the same stored procedure, function, or batch, are in the same scope.
in the case of mysql this statement returns the last auto-numeric that has been generated in the same connection that we are using currently. As long as we do not share connections (something unlikely in any case) we would have no problem obtaining it with confidence.
In Oracle’s case, it’s a bit more complex. Begin with and incredibly, Oracle did not have self-incremental fields until the appearance of its version 12c in June 2013. Before that you had to manage using Sequences and triggers to achieve the same thing.
CREATE TABLE Orders ( IDOrder INT PRIMARY KEY, .... );
We also have to create a sequence of the order id
CREATE SEQUENCE SequenceOrders MINVALUE 1 MAXVALUE 1000 INCREMENT BY 1;
And a trigger that triggers the sequence.
CREATE OR REPLACE TRIGGER orders_bit BEFORE INSERT ON Orders FOR EACH ROW BEGIN SELECT SequenceOrders.NEXTVAL INTO :new. Order ID FROM dual; NDT; As a step
As a final step, we had to perform a returning to insert
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate....) VALUES (1, 1...) RETURNING OrderID INTO :last_insert_id
In version 12c they have improved it and the sentence of creation of the incrementable auto is :
CREATE TABLE Orders (OrderID NUMBER GENERATED ALWAYS AS IDENTITY, ... );
Now, of course, to obtain the value we must do exactly the same as before in the insertion instruction, that is, use RETURNING INTO to obtain the value, as we have just seen. Learn more at https://docs.oracle.com/en/database/other-databases/nosql-database/19.1/java-driver-table/creating-tables-identity-column.html
We hope you found it useful friends! See you in the next one, if you need help with this or another database topic, do not hesitate to contact us.