In SQL, how do I handle missing foreign key values when adding new records?

Question

5.00/5 (1 vote)

See more:

Please forgive the general nature of this question, but I am struggling to find good advice on how to handle this problem.

Assume I have two SQL tables, as follows:

Employees: id, name, department_id
1, 'John', 1
2, 'Mary', 3
3, 'Cathy', 2
4, 'Karen', 1

Departments: id, name
1, 'Sales'
2, 'Admin'
3, 'Shop floor'

When I create a new employee, I would like to allow the user of the application to leave the department blank to begin with. But when I create the database record, I obviously have to supply a department_id foreign key value. At the moment, I am using -1, which can never be a legal foreign key.

My problem comes when trying to write the SQL to retrieve all the unique employees with no duplicates.

If I simply use...

SQL

SELECT e.id, e.name, d.department FROM Employees e INNER JOIN Departments d ON e.department_id = d.id;

...then I don't get any of the new employees (i.e. the ones who have not yet been assigned a department). This means the user of the application can't access these new employees and put them in a department.

If on the other hand, I use...

SQL

SELECT e.id, e.name, d.department FROM Employees e INNER JOIN Departments d ON e.department_id = d.id OR e.department_id = -1;

...I get lots of duplicate records because every employee with no department is included in the result once for each department.

I would really appreciate some advice on how best to tackle this.

What I have tried:

I have tried adding a dummy department as the first record and assigning new employees to that, but then I have to filter this out from the UI when I am doing things like presenting a list of departments, which seems less than ideal.

Posted 2-Dec-16 6:40am

Patrick Skelton

Updated 2-Dec-16 7:49am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2016-12-02T07:49:00

Change the department_id column in the Employees table to be nullable, and use NULL to indicate that a department has not been assigned. This will allow you to create the foreign key reference between the two tables.

SQL

ALTER TABLE Employees
ALTER COLUMN department_id int NULL;
GO
UPDATE Employees
SET department_id = NULL
WHERE department_id = -1;
GO
ALTER TABLE Employees WITH CHECK
ADD CONSTRAINT FK_Employees_Department
FOREIGN KEY (department_id)
REFERENCES Departments (id);
GO
ALTER TABLE Employees
CHECK CONSTRAINT FK_Employees_Department;
GO

When you want to retrieve the records, use a LEFT JOIN:

SQL

SELECT 
    e.id, 
    e.name, 
    d.department
FROM
    Employees As e
    LEFT JOIN Departments As d
    ON d.id = e.department_id
;

Employee records with no department assigned will return NULL for the department column.

Visual Representation of SQL Joins[^]