On a single row INSERT this is permitted:
INSERT INTO item (id_item, id_cart, id_product, qty, price, discount)
VALUES ( null, 221, 22, 1, 1199, 10);
But on a multi rows INSERT on the same table, it gives an error message:
INSERT INTO item (id_item, id_cart, id_product, qty, price, discount)
VALUES ( null, 221, 22, 1, 1199, 10),
( null, 221, 19, 2, 89.99, 5);
Error report - ORA-01400: cannot insert NULL into ("COMMANDE"."ITEM"."ID_ITEM")
DDL: for the table/column: CREATE TABLE "COMMANDE"."ITEM"
( "ID_ITEM" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 99999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE)
It seems to me that the order of constraints validation is not in the right order on multi rows INSERT, the GENERATED BY DEFAULT ON NULL AS IDENTITY should occur before the NOT NULL constraint.
Note: if I remove the column from the column list, it works, but I'd much rather be able to see all columns in my INSERT statement.
INSERT INTO item (id_cart, id_product, qty, price, discount) --PK column with IDENTITY removed
VALUES ( 221, 22, 1, 1199, 10),
( 221, 19, 2, 89.99, null);
Thanks
Jean-François