PostgreSQL - NULL References

Question

I have a data model for Tasks and Equipments.

Sometimes a Task needs some Equipment to be completed, sometimes it doesn't.

Task table

create table task (
    id serial primary key
,   task_name varchar(80)
)

Equipment table

 create table equipment (
        id serial primary key
    ,   equipment_name varchar(80)
    )

What I would like to do is add a null reference to the Task table.

create table task (
    id serial primary key
,   task_name varchar(80)
,   equipment_id serial null references equipment(id) 
)

I get an error

ERROR:  conflicting NULL/NOT NULL declarations for column "equipment_id" of table "task"
SQL state: 42601

How can I maintain my data integrity while adding a Foreign key to my tables so that it works as expected? I thought you could have null references.

Your code [seems to work](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=acf96e2e73c01cca746986bc3f5a288d). I am unsure what your actual issue is. — GMB, May 12 '20 at 23:27
`serial` implies `NOT NULL` but it's the wrong choice for `task.equipment_id` to begin with (see Gordon's answer) — , May 13 '20 at 21:35

score 1 · Answer 1 · answered May 13 '20 at 21:25

You have a potential modeling issue: "Task needs some Equipment" as stated. But does "some" actually mean "one", Can a Task require 2 or more Equipment? If so and assuming an Equipment can be needed by more that 1 task, you need a resolution table (Task_Equipment perhaps) to resolve the many-to-many this represents. So then

create table equipment (
       id serial primary key
     , equipment_name varchar(80)
     );

create table task (
      id serial primary key
    , task_name varchar(80)
    ); 

create table task_equipment (
       task_id       int
     , equipment_id  int 
     , constraint    te_pk 
                     primary key (task_id, equipment_id)
     , constraint    te2task_fk 
                     foreign key (task_id)
                     references task(task_id)
     , constraint    te2equipment_fk 
                     foreign key (equipment_id)
                     references equipment(equipment_id)  
     );

And I agree if your version has it use as identity instead of serial.

score 0 · Answer 2 · answered May 12 '20 at 23:27

serial is a data type that is really an integer. So, you want:

create table task (
    id serial primary key,
    task_name varchar(80),
    equipment_id int references equipment(id) 
);

That said, Postgres now recommends using int generated always as identity. This makes it clearer:

create table task (
    id int generated always as identity primary key,
    task_name varchar(80)
);

create table task (
    id int generated always as identity primary key,
    task_name varchar(80),
    equipment_id int references equipment(id) 
);

The reasons for preferring generated always as identity are technical. But one effect is that the code is more consistent -- both the primary key and foreign key have the same explicit type.

PostgreSQL - NULL References

2 Answers2