Issue
I can not insert a record in the POSTGRES database, I want the foreign key to be null.
My table:
CREATE TABLE sad_avaliado (
id BIGSERIAL NOT NULL,
tenant_id INT8 NOT NULL,
funcionario_id BIGSERIAL NOT NULL,
epoca_id BIGSERIAL NOT NULL,
cca_id BIGSERIAL,
avaliador_id BIGSERIAL NOT NULL,
apagado boolean NOT NULL,
PRIMARY KEY (id)
);
alter table sad_avaliado add constraint sad_funcionario_fkey foreign key (funcionario_id) references sad_funcionario;
alter table sad_avaliado add constraint sad_epoca_fkey foreign key (epoca_id) references sad_epoca;
alter table sad_avaliado add constraint sad_cca_fkey foreign key (cca_id) references sad_cca;
alter table sad_avaliado add constraint sad_avaliador_fkey foreign key (avaliador_id) references sad_avaliador;
My SQL Insert:
INSERT INTO public.sad_avaliado(
id, tenant_id, funcionario_id, epoca_id, cca_id, avaliador_id, apagado)
VALUES (1, 1, 1, 1, null, 1, false);
My Error:
ERROR: null value in column "cca_id" violates not-null constraint
Solution
The foreign key references for BIGSERIAL
should use BIGINT
:
CREATE TABLE sad_avaliado (
id BIGSERIAL NOT NULL,
tenant_id INT8 NOT NULL,
funcionario_id BIGINT NOT NULL,
epoca_id BIGINT NOT NULL,
cca_id BIGINT,
avaliador_id BIGINT NOT NULL,
apagado boolean NOT NULL,
PRIMARY KEY (id)
);
I think this is the one exception to the notion that foreign key references should have the same type as the primary key. I mean, the underlying type is the same, but BIGSERIAL
is used to specify that it is auto-incrementing (other databases use a separate keyword such as auto_increment
or identity
).
Answered By - Gordon Linoff