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