Why do some users and not others get ORA-01400: cannot insert NULL into primary key

Question

EDIT: I have replaced the generic SQL with the actual SQL.

I know how to fix it. I am just wondering why it worked on some developer machines and not others.

CREATE TABLE OUTPUTDEFNACTIVATION ("OUTPUTDEFNACTIVATIONID" NUMBER, "OUTPUTDEFNTYPE" NUMBER(1,0) NOT NULL, "OUTPUTDEFNID" NUMBER NOT NULL, "PROJECTVIEWID" NUMBER, "SYSTEMUSERID" NUMBER, "VISIBLE" NUMBER(3,0));
ALTER TABLE OUTPUTDEFNACTIVATION ADD CONSTRAINT OUTPUTDEFNACTIVATION_P PRIMARY KEY (OUTPUTDEFNACTIVATIONID) ENABLE;
ALTER TABLE OUTPUTDEFNACTIVATION ADD CONSTRAINT OUTPUTDEFNACTIVATION_PROJECT FOREIGN KEY (PROJECTVIEWID) REFERENCES PROJECTVIEW (PROJECTVIEWID) ON DELETE CASCADE ENABLE;
ALTER TABLE OUTPUTDEFNACTIVATION ADD CONSTRAINT OUTPUTDEFNACTIVATION_USER FOREIGN KEY (SYSTEMUSERID) REFERENCES SYSTEMUSER (SYSTEMUSERID) ON DELETE CASCADE ENABLE;
ALTER TABLE OUTPUTDEFNACTIVATION ADD CONSTRAINT UNIQUE_OUTPUTDEFNACTIVATION UNIQUE (OUTPUTDEFNTYPE, OUTPUTDEFNID, SYSTEMUSERID, PROJECTVIEWID);
CREATE SEQUENCE "OUTPUTDEFNACTIVATION_SEQ" MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

INSERT INTO OUTPUTDEFNACTIVATION (OUTPUTDEFNTYPE, OUTPUTDEFNID, SYSTEMUSERID, PROJECTVIEWID, VISIBLE) 
SELECT 0, KPIDEFNID, SYSTEMUSERID, PROJECTVIEWID, VISIBLE FROM KPIDEFN, SYSTEMUSER, PROJECTVIEW, DUAL WHERE ACTIVE = 1 AND SYSTEMUSERID <> 1 AND STATUSID IN (1,4);

My fix is

INSERT INTO OUTPUTDEFNACTIVATION (OUTPUTDEFNACTIVATIONID, OUTPUTDEFNTYPE, OUTPUTDEFNID, SYSTEMUSERID, PROJECTVIEWID, VISIBLE) 
SELECT OUTPUTDEFNACTIVATION_SEQ.NEXTVAL, 0, KPIDEFNID, SYSTEMUSERID, PROJECTVIEWID, VISIBLE FROM KPIDEFN, SYSTEMUSER, PROJECTVIEW, DUAL WHERE ACTIVE = 1 AND SYSTEMUSERID <> 1 AND STATUSID IN (1,4);

But why wasn't it necessary on all developer machines? They all have their own schema, but my script created this table for them all.

ORA-01400: cannot insert NULL into ("DEVF22FMT"."OUTPUTDEFNACTIVATION"."OUTPUTDEFNACTIVATIONID")

Answer 1

The same table behaves the same way, so - if all of them (developers) used the same INSERT statement, connected to the same database (user), they'd get the same result - either success or a failure.

---

However, if they have their own schemas, then it depends on the way they created target tables.

Behaviour you described could be result of

• identity column (12c onwards)
• database trigger

which then "automatically", in the background, populate that NOT NULL column.