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!

Juan Carlos Oropeza · Accepted Answer · 2017-08-01 16:34:11Z

9

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

edited Aug 1, 2017 at 16:34

answered Aug 1, 2017 at 16:09

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

krokodilko Over a year ago

BTW: (depno int unique not null, primary key(depno) is superfluous, primary key is by the definition not null and unique, just: depno int primary key is enough.

Juan Carlos Oropeza Over a year ago

@krokodilko You are right, I fix the employee table but didn't check the department one. Also you can reference the foreign key on the same line you declare the field.

Juan Carlos Oropeza Over a year ago

Also you should use SERIAL type for your PK, and let postgres handle the ID.

Collectives™ on Stack Overflow

Postgresql include "optional", nullable foreign key?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related