How to set up auto-increment in oracle

There is no built-in auto-increment feature in Oracle database, but there are many ways to simulate it. The following are some methods for simulating auto-increment:

1. Sequence

Sequence is an object used to generate unique numbers in Oracle database, similar to auto-increment . Sequences can be created separately before or after the table is created, and can be changed as needed.

First, create the sequence:

CREATE SEQUENCE seq_name
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

This will create a sequence called seq_name with a start value of 1, a step size of 1, and no maximum value.

Then, apply the sequence to the table:

CREATE TABLE table_name (
  id NUMBER(10) DEFAULT seq_name.NEXTVAL PRIMARY KEY,
  ...
);

In this example, assign the NEXTVAL attribute of the sequence as the default value to the id column, and define the id column as the primary key.

Now, every time you insert a new row into the table, a unique auto-incrementing ID is automatically assigned.

2. Trigger(trigger)

Another way to simulate auto-increment is to use a trigger. This requires a trigger on the table that automatically assigns a unique number every time a row is inserted.

First, create the trigger:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  SELECT seq_name.NEXTVAL INTO :new.id FROM dual;
END;

This will automatically assign a sequence next value before inserting the row.

Then, apply the trigger to the table:

CREATE TABLE table_name (
  id NUMBER(10) PRIMARY KEY,
  ...
);

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  SELECT seq_name.NEXTVAL INTO :new.id FROM dual;
END;

In this example, define the id column as the primary key, and create a trigger named trigger_name whenever a new row is inserted. Automatically assign a unique number to the id column.

3. Unique identifier column(unique identifier column)

The last method is to create a unique identifier column in the table that uses GUID or UUID as the unique identifier . This method is easier to implement than the previous two methods, but it has some disadvantages, such as identifiers not being sequential.

First, create the table:

CREATE TABLE table_name (
  id VARCHAR2(36) PRIMARY KEY DEFAULT sys_guid(),
  ...
);

This will create a unique identification column called id, automatically assigning a unique GUID using the sys_guid() function.

Then, insert the data into the table:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

In this example, each time a new row is inserted, a unique GUID is automatically assigned.

No matter which method you use to simulate auto-increment, you should pay attention to some special cases, such as insertion failure and assigned IDs on rollback. At the same time, appropriate methods are selected to design the system based on actual application scenario requirements.

The above is the detailed content of How to set up auto-increment in oracle. For more information, please follow other related articles on the PHP Chinese website!