Nullable foreign keys and creating join tables

Question

I'm not that experienced in sql modeling, but i ́m thinking of an intricate problem (or/and a silly one).

I have a project table(table 1) with many projectassignments(table 2), which is a join table. This join table have one person(table 3) with many projectassignments. I also have two tables(table 4 and 5) representing two different tests that can have many projectassignments.

But normally when you create a project you do it to assign people to one test, so the other testtable for this assignment would be null. Is it possible that the foreign keys on the join table for these tests could be nullable ints? what would be the implications for doing this?

Project table
int ID
ICollection<ProjectAssignment> ProjectAssignments { get; set; }
ProjectAssignments table
int ID
int ProjectID
int PersonID
int Test1ID
int Test2ID
Person table
int ID
ICollection<ProjectAssignment> ProjectAssignments { get; set; }
Test1 table
int ID
ICollection<ProjectAssignment> ProjectAssignments { get; set; }
Test2 table
int ID
ICollection<ProjectAssignment> ProjectAssignments { get; set; }

Any feedback much appreciated!

• Neil: A foreign key can be null, if it isn't applicable. Though you should do it only when it makes sense to do so. If there must be an equivalent in another table, it makes no sense that the foreign key to that table can be set to null.
• JimmyJames: Why is there a Test1 table and a Test2 table? This suggests a problem with the model.

Answer 1

I'm surprised that both of the currently posted answers have glossed over the simple truth that optional relationships are both perfectly fine to use, and easily implemented.

Is it possible that the foreign keys on the join table for these tests could be nullable ints?

Yep, it's possible. And you've somewhat already answered your own question here, you do so by using nullable ints.

public class Parent
{
 public int Id { get; set; }
 public ICollection<Child> Children { get; set; }
}
public class Child
{
 public int Id { get; set; }
 public int? ParentId { get; set; } // <-- this makes it nullable
 public Parent Parent { get; set; }
}

By making that ParentId of type int? instead of int, the relationship is now nullable.

What would be the implications for doing this?

You will no longer have a cascaded delete by default. When deleting the parent, the children's relationship to the now-deleted parent will be removed, but the children themselves will not be removed.

If you've already written some queries, make sure to account for the possibility that both Parent and ParentId can be null.

Other than that, the consequences of having a nullable relationship are straightforward, i.e. having the ability to not fill in a value for the FK property.

Answer 2

If a foreign key can be Null = optional, it kind of forfeits the idea of 'foreign key'.

In this case, I would think the relationship should be defined the other way round - the 'foreign' object's table should instead have column with the ID of the respective main object. That way, it can't be null.

There could be performance reasons to build it reverted, but only if it is really necessary.

Answer 3

There is only one case where you should have a nullable FK and that is where a table joins to itself, ie. a parent/child relationship.

In you case you don't need nullable FKs, your confusion is in trying to use the projectassignments table to do two many to many joins.

Instead maybe add two new 'join tables' or reconsider your relationships its not totally clear

project 
 id
projectAssignments
 id
 projectId
 personId
person
 id
test1Assignments
 projectAssignmentId
 test1Id
test2Assignments
 projectAssignmentId
 test2Id
test1
 id
test2
 id

• Greg Burghardt: A nullable foreign key is fine when a relationship between a record in one table to another is optional. The parent-child relationship isn't the only use case for a nullable foreign key.
• Ewan: you could do that relationship the other way around with the fk in the other table
• Caleth: @Ewan not in an "optional one to many" relation