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.
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).
2
Yes, the problem is with the combination of INSERT ALL and an IDENTITY column.
If you change the CREATE TABLE so the that you generate a named constraint for the PRIMARY KEY column:
CREATE TABLE workers(
id NUMBER
GENERATED ALWAYS AS IDENTITY
CONSTRAINT workers__id__pk 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)
);
Then the error raised is:
ORA-00001: unique constraint (SCHEMA_NAME.WORKERS__ID__PK) violated
This appears to be because the IDENTITY value is only being generated once for the INSERT ALL statement and all rows are being assigned the same id value which then violates the PRIMARY KEY constraint.
To get round the error, you can use INSERT INTO ... SELECT ... :
INSERT INTO workers (last_name, first_name, patronymic, email, salary, bonus, phone_number)
SELECT 'Jest', 'Warner', null, '[email protected]', 758.94, 56.03, '+375448743018' FROM DUAL UNION ALL
SELECT 'Mose', 'Red', null, '[email protected]', 1082.65, 119.55, '+375257502777' FROM DUAL UNION ALL
SELECT 'Stowers', 'Chase', 'Blincowe', '[email protected]', 2662.34, 138.64, '+375338380687' FROM DUAL;
If you have a homework task that requires you to use INSERT ALL then, rather than trying to insert multiple rows into one table, you can insert one row into multiple tables:
CREATE TABLE other (
id NUMBER
GENERATED ALWAYS AS IDENTITY
CONSTRAINT other__id__pk PRIMARY KEY
);
Then:
INSERT ALL
INTO workers (id, last_name, first_name, patronymic, email, salary, bonus, phone_number) VALUES (DEFAULT, 'Jest', 'Warner', null, '[email protected]', 758.94, 56.03, '+375448743018')
INTO other (id) VALUES (DEFAULT)
SELECT * FROM dual;
fiddle