Menu
I would like to achieve an identity or auto-incrementing value in a column ala SQL Server:
CREATE TABLE RollingStock
(
Id NUMBER IDENTITY(1,1),
Name Varchar2(80) NOT NULL
);
How can this be done?
579
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
The advantages to using numeric, auto incremented primary keys are numerous, but the most impactful benefits are faster speed when performing queries and data-independence when searching through thousands of records which might contain frequently altered data elsewhere in the table.
When you define a column in MySQL, you can specify a parameter called AUTO_INCREMENT. Then, whenever a new value is inserted into this table, the value put into this column is 1 higher than the last value. But, Oracle does not have an AUTO_INCREMENT feature.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
As Orbman says, the standard way to do it is with a sequence. What most people also do is couple this with an insert trigger. So, when a row is inserted without an ID, the trigger fires to fill out the ID for you from the sequence.
CREATE SEQUENCE SEQ_ROLLINGSTOCK_ID START WITH 1 INCREMENT BY 1 NOCYCLE;
CREATE OR REPLACE TRIGGER BI_ROLLINGSTOCK
BEFORE INSERT ON ROLLINGSTOCK
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
select SEQ_ROLLINGSTOCK_ID.NEXTVAL
INTO :NEW.ID from dual;
END;
This is one of the few cases where it makes sense to use a trigger in Oracle.
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
