SQL – Handling Nullable Foreign Keys and Creating Join Tables

entity-frameworksqlsql server

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!

Best Answer

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.

Related Topic