Postgresql include "optional", nullable foreign key?

Question

So I have a probably stupid problem and I don't even know if I'm trying to solve it the right way, but here it is.

I have a department with several employees, an employee can be assigned to a department but doesn't have to be. I thought of using the department no as foreign key in employee, but what about employees who are not part of a department? Can I just insert null in the fk column? But I'm wondering if that even works because can't I insert only values that exist in the referenced column?

Here's my relevant code

CREATE TABLE department(depno int unique not null,
                        primary key(depno));

CREATE TABLE employee(ID int unique not null, name not null,
                      depno int,
                      primary key(ID))
                      foreign key(depno)
                      references department(depno)

I hope this question hasn't been answered a hundred times already and I just haven't found my answer while searching for it for the last two hours. But I just can't seem to find anything about whether this should work or not.

Any tips or solutions would be greatly appreciated! Thank you so much already!

Answer 1

This work in postgresql

SQL DEMO

CREATE TABLE department(depno int PRIMARY KEY);

CREATE TABLE employee(ID int PRIMARY KEY,
                      name text not null,
                      depno int references department(depno)
                      );


INSERT INTO department  VALUES (1);
INSERT INTO department  VALUES (2);

INSERT INTO employee  VALUES (1, 'Jhon Doe', null); -- INSERT OK
INSERT INTO employee  VALUES (2, 'Jane Doe', 3);    -- VIOLATE FK

SELECT * FROM employee;

OUTPUT