0

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!

Improve this question
2
  • 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. Commented Apr 26, 2018 at 9:22
  • 2
    Why is there a Test1 table and a Test2 table? This suggests a problem with the model. Commented Jun 25, 2018 at 18:18

3 Answers 3

Reset to default
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.

Improve this answer
-1

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.

Improve this answer
-2

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
Improve this answer
3
  • 4
    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. Commented Apr 26, 2018 at 17:01
  • you could do that relationship the other way around with the fk in the other table Commented Apr 26, 2018 at 17:25
  • 1
    @Ewan not in an "optional one to many" relation Commented Jul 26, 2018 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.