Search code examples
sqloracleoracle-apex

ORA-00001: unique constraint (WKSP_MYSCHEMA.SYS_C00165018598) violated

Error ORA-00001: unique constraint (WKSP_MYSCHEMA.SYS_C00165018598) violated is occured when I'm trying to insert some data to my table named workers.

This is my CREATE TABLE statement:

CREATE TABLE workers(
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    last_name VARCHAR2(20) NOT NULL,
    first_name VARCHAR2(20) NOT NULL,
    patronymic VARCHAR2(20),
    email VARCHAR2(30),
    salary NUMBER(7,2) DEFAULT 0 NOT NULL,
    bonus NUMBER(6,2) DEFAULT 0 NOT NULL,
    phone_number CHAR(13) NOT NULL,
    CONSTRAINT check_negative_salary CHECK (salary >= 0),
    CONSTRAINT check_negative_bonus CHECK (bonus >= 0)
);

This is the data I want to insert:

INSERT ALL
INTO workers (last_name, first_name, patronymic, email, salary, bonus, phone_number) VALUES ('Jest', 'Warner', null, '[email protected]', 758.94, 56.03, '+375448743018')
INTO workers (last_name, first_name, patronymic, email, salary, bonus, phone_number) VALUES ('Mose', 'Red', null, '[email protected]', 1082.65, 119.55, '+375257502777')
INTO workers (last_name, first_name, patronymic, email, salary, bonus, phone_number) VALUES ('Stowers', 'Chase', 'Blincowe', '[email protected]', 2662.34, 138.64, '+375338380687')
SELECT * FROM dual;

I checked my table via SELECT * FROM workers and there's no data in it.

The problem may be in this line id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, but I don't know if it's true or not.

Solution

  • From the documentation:

    Restrictions on Multitable Inserts.

    Multitable inserts are subject to the following restrictions:

    …

    You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.

    An identity column is really just a wrapper around a sequence, so the same restrictions apply.

    It’s not quite correct to say you can’t use a sequence - but in practice you can’t, because of this behaviour.

    You will have to do separate single inserts, rather than using insert all; either three inserts with values clauses, or insert … select (as @MT0 showed).