-- Oracle 12c: Auto number/index value can be easily setup as shown below


CREATE TABLE tst_dup_12c
(tst_id      NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
 tst_val     NUMBER,
 tst_date    TIMESTAMP(3) DEFAULT SYSTIMESTAMP(3) NOT NULL
);

INSERT INTO tst_dup_12c(tst_id, tst_val)  VALUES(100, 1);

INSERT INTO tst_dup_12c(tst_val) VALUES(2);

COMMIT;

SELECT tst_id, tst_val FROM tst_dup_12c;

-- Output of above query:
-- In first insert it took tst_id value of 100 and second time it was 
-- auto generated with value of 1

    TST_ID    TST_VAL
---------- ----------
       100          1
         1          2

--Insert duplicate tst_id data

INSERT INTO tst_dup_12c(tst_id, tst_val)
SELECT level, level*10
FROM DUAL 
CONNECT BY level <= 4;

COMMIT;

--Duplicate tst_id of 1 in table, since it is not a primary key
--If the table is created as shown below, the third insert will fail


    TST_ID    TST_VAL
---------- ----------
       100          1
         1          2
         1         10
         2         20
         3         30
         4         40


CREATE TABLE tst_unique_12c
(tst_id      NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, 
 tst_val     NUMBER,
 tst_date    TIMESTAMP(3) DEFAULT SYSTIMESTAMP(3) NOT NULL
);


INSERT INTO tst_unique_12c(tst_id, tst_val)  VALUES(100, 1);

INSERT INTO tst_unique_12c(tst_val) VALUES(2);

COMMIT;

-- This will result in ORA-00001 unique constraint violation error
-- The tst_id is specified as primary key as tst_id 1 already exists

INSERT INTO tst_unique_12c(tst_id, tst_val)
SELECT level, level*10
FROM DUAL 
CONNECT BY level <= 4;

COMMIT;

-- The sequence value can be revised by ALTER TABLE

ALTER TABLE tst_unique_12c MODIFY tst_id
GENERATED AS IDENTITY (START WITH LIMIT VALUE);

--The script also excludes the tst_id, since it is reset to highest 
--value (100)


INSERT INTO tst_unique_12c(tst_val)
SELECT  level*10  FROM DUAL 
CONNECT BY level <= 4;

COMMIT;


    TST_ID    TST_VAL
---------- ----------
       100          1
         1          2
       101         10
       102         20
       103         30
       104         40


--Other way to reset is as shown below by specifying MINVALUE

ALTER TABLE tst_unique_12c MODIFY tst_id
GENERATED AS IDENTITY (MINVALUE 100)
;