Oracle 12c: Insert into Table with Identity Column

Question

I have a table with one column of type Identity Column, which is also the primary key.

CREATE  TABLE identity_demo  (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    description VARCHAR2(100) not null
  );

then I insert a few lines of data

insert into identity_demo (id, description) values (1,'A');
insert into identity_demo (id, description) values (2,'B');
insert into identity_demo (id, description) values (3,'C');
insert into identity_demo (id, description) values (4,'D');
insert into identity_demo (id, description) values (5,'E');
insert into identity_demo (id, description) values (6,'F');

if I now want to insert a row for which the value ID is not set, I get a key violation

insert into identity_demo (description) values ('G');

ORA-00001: Unique Constraint (UWE.IDENTITY_DEMO_PK) verletzt what's the best way to proceed here?

Answer 1

First of all, seems that there's a part , CONSTRAINT IDENTITY_DEMO_PK PRIMARY KEY (id)

added to the end of the table creation DDL.

Just remove BY DEFAULT ON NULL part in order to leave the management of identity generation to the DBMS while keeping ID column as a PRIMARY KEY. In this case, you should remove ID column from the column list within the Insert Statement like in

INSERT INTO identity_demo (description) VALUES ('G'); :

SQL> CREATE  TABLE identity_demo  (
  2      id          NUMBER GENERATED AS IDENTITY,
  3      description VARCHAR2(100) NOT NULL,
  4      CONSTRAINT IDENTITY_DEMO_PK PRIMARY KEY (id)
  5    );

Table created

SQL> BEGIN
  2    INSERT INTO identity_demo (id, description) VALUES (1,'A');
  3    INSERT INTO identity_demo (id, description) VALUES (2,'B');
  4    INSERT INTO identity_demo (id, description) VALUES (3,'C');
  5    INSERT INTO identity_demo (id, description) VALUES (4,'D');
  6    INSERT INTO identity_demo (id, description) VALUES (5,'E');
  7    INSERT INTO identity_demo (id, description) VALUES (6,'F');
  8  END;
  9  /

ORA-32795: cannot insert into a generated always identity column
ORA-06512: at line 3

SQL> INSERT INTO identity_demo (description) VALUES ('G');

1 row inserted

SQL> SELECT * FROM identity_demo;

        ID DESCRIPTION
---------- -------------------------------------------------------
         1 G

SQL> BEGIN
  2    INSERT INTO identity_demo (description) VALUES ('A');
  3    INSERT INTO identity_demo (description) VALUES ('B');
  4    INSERT INTO identity_demo (description) VALUES ('C');
  5    INSERT INTO identity_demo (description) VALUES ('D');
  6    INSERT INTO identity_demo (description) VALUES ('E');
  7    INSERT INTO identity_demo (description) VALUES ('F');
  8  END;
  9  /

PL/SQL procedure successfully completed

SQL> SELECT * FROM identity_demo;

        ID DESCRIPTION
---------- --------------------------------------------------------
         1 G
         2 A
         3 B
         4 C
         5 D
         6 E
         7 F

7 rows selected