Create Auto-Increment Sequence in Oracle
In Oracle database, you can create auto-increment sequences using the following method:
- Use the CREATE SEQUENCE statement to create a sequence, specifying its starting value, increment, minimum value, and maximum value properties.
For example, the following statement creates a sequence named seq_id with a starting value of 1, incrementing by 1, with a minimum value of 1 and a maximum value of 9999999:
CREATE SEQUENCE seq_id
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999;
- In tables where auto-incrementing sequence is needed, use a sequence as the default value for a column.
For example, this statement creates a column named id in a table called employees, with seq_id sequence set as its default value.
CREATE TABLE employees (
id NUMBER DEFAULT seq_id.NEXTVAL,
name VARCHAR2(50),
salary NUMBER
);
- When inserting data, it is not necessary to specify the value of the id column, as the database will automatically use the next value in the sequence.
For example, the following statement inserts a record into the employees table, where the value for the id column will be automatically retrieved from the seq_id sequence.
INSERT INTO employees (name, salary) VALUES ('John Doe', 5000);
It is important to note that auto-increment sequences are at the database level and not limited to a single table. When using the same sequence across multiple tables, you can specify the sequence’s name as a default value in the CREATE TABLE statement, or use the NEXTVAL function of the sequence to retrieve the next value and insert it into the table.