CODEWITHSUNDEEP
sqlpostgresqlforeign-keyssql-insertcreate-table
tomaslb26
tomaslb26

Reputation: 35

ERROR: null value in column violates not-null constraint

CREATE TABLE medico
    (num_cedula serial NOT NULL UNIQUE,
     nome varchar(20) NOT NULL,
     especialidade varchar(20) NOT NULL,
     PRIMARY KEY(num_cedula));

CREATE TABLE consulta
    (num_cedula serial NOT NULL,
     num_doente serial NOT NULL,
     data_consulta date NOT NULL,
     nome varchar(20) NOT NULL,
     CHECK(EXTRACT(DOW FROM data_consulta) NOT IN (6,0)),
     UNIQUE(num_doente,data_consulta,nome),
     FOREIGN KEY(nome) REFERENCES instituicao(nome) ON UPDATE CASCADE,
     FOREIGN KEY(num_cedula) REFERENCES medico(num_cedula) ON UPDATE CASCADE,
     PRIMARY KEY(num_cedula,num_doente,data_consulta));

CREATE TABLE analise
    (num_analise serial NOT NULL UNIQUE,
     especialidade varchar(20) NOT NULL,
     num_cedula serial ,
     num_doente serial ,
     data_analise date ,
     data_registo date NOT NULL,
     nome varchar(20) NOT NULL,
     quant integer NOT NULL,
     inst varchar(20) NOT NULL,
     CONSTRAINT fk_analise FOREIGN KEY(num_cedula,num_doente,data_analise) REFERENCES consulta(num_cedula,num_doente,data_consulta) ON UPDATE CASCADE,
     FOREIGN KEY(inst) REFERENCES instituicao(nome) ON UPDATE CASCADE,
     PRIMARY KEY(num_analise));

This is my code but when I try to insert another "analise" with num_cedula,num_doente,data_analise as NULL it gives me this error ("ERROR: null value in column "num_cedula" violates not-null constraint DETAIL: Failing row contains (32, Ortopedia, null, null, null, 2019-12-02, glicemia, 176, Instituicao1). SQL state: 23502") and I don't really know why. Also, this is the insert code I used:

INSERT INTO analise VALUES(32,'Ortopedia',NULL,NULL,NULL,'2019-12-02','glicemia',176,'Instituicao1');

Upvotes: 1

Views: 4603

Answers (1)

GMB
GMB

Reputation: 222382

The serial datatype just does not allow null values. The documentation explains what happens under the hood when declaring a serial (emphasis is mine):

Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted.

I don't think that you really want serial here. The use case for that datatype is to create an auto-incremented column. You could probably use int instead, that would avoid the problem you are getting here.

Upvotes: 2

Related Questions