How to create id with AUTO_INCREMENT on Oracle?

In Oracle, you can create an auto-increment primary key column using the SEQUENCE object along with a trigger or by using the IDENTITY column feature available in Oracle Database 12c and later versions.

Here are both methods:

1. Using SEQUENCE and Trigger:

   First, you need to create a sequence:

   CREATE SEQUENCE table_name_seq
       START WITH 1
       INCREMENT BY 1
       NOMAXVALUE;
   

   Then, create a trigger to automatically populate the primary key column with the next value from the sequence:

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

   Replace table_name and id with your actual table and primary key column names.

2. Using IDENTITY column (Oracle 12c and later):

   In Oracle 12c and later versions, you can use the IDENTITY column to automatically generate unique values for a primary key column.

   CREATE TABLE table_name (
       id NUMBER GENERATED BY DEFAULT AS IDENTITY,
       -- other columns
       CONSTRAINT table_name_pk PRIMARY KEY (id)
   );
   

   When a new row is inserted into the table, Oracle automatically assigns a unique value to the id column. The sequence handling is done internally by Oracle, and you don't need to create a separate sequence or trigger.

Choose the method based on your Oracle version and requirements. The IDENTITY column feature is simpler and more efficient, but it's available only in Oracle Database 12c and later versions. If you're using an older version of Oracle, you'll need to use the sequence and trigger method.