Why this simple insert query on an Oracle database can't work?

Question

I am pretty new in database and I have the following problem trying to implement an insert query that create a row into a table named CODA_RX that have the following structure (obtained by performing the describe CODA_RX statment:

Name                          Null     Type           
----------------------------- -------- -------------- 
PK_CODA                       NOT NULL NUMBER(10)     
FK_TIPO_DOC                            NUMBER(4)      
FK_PIVA_DESTINATARIO                   VARCHAR2(16)   
FK_CDZZ                                VARCHAR2(4)    
DATA_IN                                DATE           
DATA_OUT                               DATE           
NUM_DOC                                VARCHAR2(35)   
FK_STATO                      NOT NULL NUMBER(2)      
CANALE                                 VARCHAR2(3)    
SIZE_XML                               NUMBER(10)     
FK_PIVA_MITTENTE                       VARCHAR2(20)   
INDIRIZZAMENTO                         VARCHAR2(100)  
SEGNALAZIONE                           VARCHAR2(4000) 
FORNITORE                              VARCHAR2(100)  
MATRICOLA_VERIFICATORE                 VARCHAR2(16)   
MATRICOLA_APPROVATORE                  VARCHAR2(16)   
DATA_INSERIMENTO_VERIFICATORE          DATE           
DATA_LAVORAZIONE_APPROVATORE           DATE           
MOTIVO_RIFIUTO                         VARCHAR2(1000) 
TOT_FATTURA                            NUMBER(10,2)   
DATA_DOC                               DATE           
DATA_SCADENZA_DOC                      DATE           
CIG                                    VARCHAR2(10)   
CUP                                    VARCHAR2(15)

Ok, it seems that the only 2 field that can not be NULL are the PK_CODA (that I think is the primary key of the table, is it right? how can I check it?) and the FK_STATO fields.

So I want to insert a new record into this table. If the PK_CODA is the primary key I think that it shoud be automatically generated by Oracle.

So I tryied to perform this statment that insert a new record setting only the FK_STATO field:

insert into CODA_RX (FK_STATO) values (2);

But the problem is that I obtain this error message:

Errore con inizio alla riga 5 nel comando: insert into CODA_RX (FK_STATO) values (2) Report errori: Errore SQL: ORA-01400: cannot insert NULL into ("EDIADD"."CODA_RX"."PK_CODA") 01400. 00000 - "cannot insert NULL into (%s)" *Cause:
*Action:

So it seems that the PK_CODA field can not be null, so what it means that it is not automatically generated when I try to insert a new record into the table? Or there is some other syntax error into my insert query? What am I missing?

Tnx

Answer 1

A primary key cannot be NULL.

No, it is not autogenerated. If you are used to work with SQL Server or postgresql this can be strange. But you must ask the value to a sequence. Take a look here: How to create id with AUTO_INCREMENT on Oracle?

Answer 2

insert into CODA_RX (FK_STATO) values (2);

The above insert statement will insert a row having a single value for the FK_STATO column, however, it will insert NULL values for the rest of the columns.

You cannot insert NULL values in the primary key column. That is the reason you see the NOT NULL constraint for the PK_CODA column.

If you want to populate the primary key automatically for every insert, then you need to use:

• Identity column introduced in 12c
• Trigger and sequence

Since, you are on 11g, you need to use the trigger-sequence approach.

For example,

TABLE:

SQL> CREATE TABLE t (
  2    ID           NUMBER(10)    NOT NULL,
  3    text  VARCHAR2(50)  NOT NULL);

Table created.

SQL>

PRIMARY KEY to be populated by the sequence:

SQL> ALTER TABLE t ADD (
  2    CONSTRAINT id_pk PRIMARY KEY (ID));

Table altered.

SQL>

SEQUENCE to support the primary key:

SQL> CREATE SEQUENCE t_seq
  2  START WITH 1000
  3  INCREMENT BY 1;

Sequence created.

SQL>

TRIGGER

If you do not want to have the sequence in the INSERT , you could automate it via TRIGGER.

SQL> CREATE OR REPLACE TRIGGER t_trg
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  WHEN (new.id IS NULL)
  5  BEGIN
  6    SELECT t_seq.NEXTVAL
  7    INTO   :new.id
  8    FROM   dual;
  9  END;
 10  /

Trigger created.

SQL>

INSERT

SQL> INSERT INTO t(text) VALUES('auto-increment test 1');

1 row created.

SQL> INSERT INTO t(text) VALUES('auto-increment test 2');

1 row created.

SQL>

Let’s see if we have the ID column auto-incremented with the desired values-

SQL> SELECT * FROM t;

   ID TEXT
----- --------------------------------------------------
 1000 auto-increment test 1
 1001 auto-increment test 2

SQL>

So, the ID column now starts with value 1000 and increments by 1 with subsequent inserts.

Answer 3

if you are using sequence for PK_CODA then sql should be insert into CODA_RX (PK_CODA,FK_STATO) values (PK_CODA_seq.nextval, 2);

else if you want to use some default value for PK_CODA you need to define at time of table creation.