CODEWITHSUNDEEP
sqloracle-database
user8078246
user8078246

Reputation:

ORA-01400: cannot insert NULL into even if I'm not inserting that column

I get this error: ORA-01400: cannot insert NULL into ("GRT2158A"."BUVETTE"."NUMB").

Here is my code:

INSERT INTO Buvette(Emplacement, NumS)
SELECT Emplacement, NumS
FROM GILLES_HUBERT.tp4_buvettes;

What I do understand: NUMB is a primary key with a constraint to check if it's not null. Then, I cannot insert NULL there.

What I don't understand: I'm not even inserting the NUMB column, just Emplacement and NumS. Why does it complain about NUMB?

Upvotes: 1

Views: 10946

Answers (2)

MT0
MT0

Reputation: 168440

If you are not providing any value for a column then that column's value will be created with its default value and, if you have not specified a default, then NULL will be inserted.

So:

CREATE TABLE Buvette(
  NumB        NUMBER CONSTRAINT Buvette__Numb__PK PRIMARY KEY,
  Emplacement NUMBER,
  NumS        NUMBER DEFAULT 4
);

INSERT INTO (Emplacement) VALUES ( 1 );

is the equivalent of doing:

INSERT INTO (NumB, Emplacement, NumS) VALUES ( NULL, 1, 4 );

Where NumB has no default so NULL is inserted but NumS has a default of 4 so 4 is inserted.

In Oracle 12c, you can now specify a sequence as the default:

CREATE SEQUENCE Buvette__NumB__Seq;

CREATE TABLE Buvette(
  NumB        NUMBER DEFAULT Buvette__NumB__Seq.NEXTVAL
                     CONSTRAINT Buvette__Numb__PK PRIMARY KEY,
  Emplacement NUMBER,
  NumS        NUMBER DEFAULT 4
);

or you can use an IDENTITY column:

CREATE TABLE Buvette(
  NumB        NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 )
                     CONSTRAINT Buvette__Numb__PK PRIMARY KEY,
  Emplacement NUMBER,
  NumS        NUMBER DEFAULT 4
);

Then you can just use your statement and the default value for NumB will be generated:

INSERT INTO Buvette(Emplacement, NumS)
SELECT Emplacement, NumS
FROM GILLES_HUBERT.tp4_buvettes;

In earlier versions, you will want to use the sequence in the INSERT statement to generate an incrementing primary key:

INSERT INTO Buvette ( NumB, Eplacement, NumS )
SELECT Buvette__NumB__Seq.NEXTVAL,
       Emplacement,
       NumS
FROM   GILLES_HUBERT.tp4_buvettes;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270733

But you are inserting NULL -- or at least the default value. You don't include it in the columns getting a value, so it needs to get some value. Apparently, there is no default.

In Oracle, this would generally mean that a trigger is not assigned to the table. You'll need to learn how to insert a unique value into the column. This is often handled through a sequence:

INSERT INTO Buvette(NumB, Emplacement, NumS)
    SELECT buvette_sequence.nextval, Emplacement, NumS
    FROM GILLES_HUBERT.tp4_buvettes;

This assumes that a sequence is defined for the table -- I don't know if that is actually the case.

Upvotes: 0

Related Questions