Oracle 12c: Insert into Table with Identity Column

user_9654456 0
Viewed 3811

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?

oracle12c sql-insert ddl identity oracle
2 Answers

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
user_58413060

You got a collision between the identity values that you have inserted manually (1 to 6) and the identity value that the sequence generator produced. You can fix it by adjusting the sequence generator:

ALTER TABLE identity_demo MODIFY (
  id GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH LIMIT VALUE));

The START WITH LIMIT VALUE locks the table, finds the highest id and sets the internal sequence generator to the next higher value 7.

If you don't want to run this ALTER TABLE command, you could separate the number spaces, say 1...999 for manual values and 1000... for automatic values. You have to set up your generator like thus:

ALTER TABLE identity_demo MODIFY (
  id GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1000));

INSERT INTO identity_demo (description) VALUES ('H');

...
6      F
7      G
1000   H
user_18640290
Related