ForeignKey `to_field` doesn't require `null=false`

Django Internals ORM
1

hi :waving_hand:
i was wondering if there is a reason when we use a to_field in a ForeignKey, the target field is not enforeced to be NOT NULL

we ran some tests, like: Dry ORM and having null as target will silently save null in the fk column, so no relationship will form

so in the example you can see:

class Target(models.Model):
    fk_target = models.IntegerField(null=True, unique=True)


class Child(models.Model):
    target = models.ForeignKey(Target, on_delete=models.CASCADE, to_field="fk_target", null=True)

so Child has an fk to Target, but instead of pointing to the id field, it points to fk_target
but since fk_target is null=True, you can do this:

target_null = Target.objects.create(fk_target=None)
child_null = Child.objects.create(target=target_null)

which seems like a normal django code, but then you query child_null

child_null_refresh = Child.objects.get(pk=child_null.pk)
print(child_null_refresh.target)
# None

and there is no relationship

i think null=false should be enforced, or if there is a reason for it to not be, maybe a warning should be logged when creating a relationship to a null column
or at least document it

should mention that unique=True is already enforced here as you can see here:

1 Like
2

<opinion>
The effects all seem reasonable and make sense to me.

I think the underlying mistake here is thinking of Null is a value of some type, when in reality, Null is the absence of a value. As such, it is never a match in a comparison. (Null != Null)

The effect being displayed is consistent with standard SQL and isn’t behavior limited to the Django ORM. Like many other edge cases in SQL, I don’t think it’s something that can (nor should) be addressed by the Django documentation.
</opinion>

For example, in psql:

test=# select where 1=1;
--
(1 row)

but

test=# select where null=null;
--
(0 rows)

Likewise, this SQL statement:

SELECT * from te_tetarget where fk_target = Null;

will return 0 rows from the table, where this query:

SELECT * from te_tetarget where fk_target is Null;

will return the rows containing Null in fk_target.

And, along these same lines, the unique=True attribute on the fk_target field does not apply to the null values. You can create as many instances of Target with fk_target = None. (Unless you use the nulls_distinct on a UniqueConstraint, and that only works with Postgresql 15+.)

Now, if you look at the statement:

child_null = TeChild.objects.select_related('target').get(pk=3)

(TeChild in the name of your Child table in my test environment, and pk=3 is the instance where target is Null.)
It results in the following SQL statement being issued (reformatted for clarity):

SELECT "te_techild"."id", "te_techild"."target_id", "te_tetarget"."id", "te_tetarget"."fk_target" 
FROM "te_techild" 
  LEFT OUTER JOIN "te_tetarget" 
    ON ("te_techild"."target_id" = "te_tetarget"."fk_target") 
    WHERE "te_techild"."id" = 3

(te is the name of the app in my test system where this is being demonstrated.)

So if either te_techild.target_id or te_tetarget.fk_target are null, this predicate will never be true.

1 Like
3

hi, sorry for delay :folded_hands:

so it is correct that null != null, but my issue goes the otherway around

i made this repo as an example: GitHub - amirreza8002/django_fk_test
(for people who don’t use uv, you can just do pip install -r requiremets.txt, or pip install -r requirements.txt --group=ipython for ipython shell)

so i make some test data:


In [1]: author_with_target = Author.objects.create(name="bob", fk_target=1)

In [2]: book_with_relationship = Book.objects.create(name="my book", author=author_with_target)

In [3]: book_with_relationship.author_id
Out[3]: 1

In [4]: book_with_relationship.author
Out[4]: <Author: Author object (1)>

In [5]: author_without_target = Author.objects.create(name="tom")

In [6]: book_with_null_relationship = Book.objects.create(name="his book", author=author_without_target)

In [7]: book_with_null_relationship.author
Out[7]: <Author: Author object (2)>

In [8]: book_with_null_relationship.author_id

In [9]: book_with_no_rel = Book.objects.create(name="whee")

In [10]: book_with_no_rel.author

In [11]: book_with_no_rel.author_id

i’m making three instances of Book:

  1. with a relationship to an Author that has fk_target set to a real value (block [2])
  2. with a relationship to an Author with null fk_target (block [6])
  3. with a relationship to no Authors (block [9])

so first one works great

but the second one, when you call create(), it looks like it made a relationship, even on block [7], you can see it shows an object as author

but, as you can see on block [8], there is no author_id

so while it looks like it has created a relationship between that book and an author, there is no relationship

when querying the database, we see the same thing:

id  name      author_id
--  --------  ---------
1   my book   1        
2   his book           
3   whee

as you can see, book 2 and 3 neither have a relationship to Author, but the difference is, with the second one we actually wanted a relationship
but without us knowing, no relationship is made

4

No, it doesn’t.

You have a field in Author, fk_target, that can have null values.

You can never relate a Book object to an Author in the database with a null fk_target, because your Book.author field value will never be equal to it. Why? Because Null != Null.

Correct, for all the reasons I described above.

There is no matched relationship possible between a foreign key of Null and a related value of Null, because Null != Null.

You might want there to be a match, but SQL doesn’t allow it.

(Again, this isn’t a Django issue. This is a characteristic of Null within a relational database.)

However, it appears to be working when you’re creating the objects initially, because you’re not looking at the database. These are Python object references in memory that do not translate to valid table relationships. As long as you’re working with the objects in memory, there is a relationship between them. But these relationships do not persist.

5

hi again :waving_hand:

yes, so this is why i think enforcing NOT NULL is a sane default to have
because having a target of null will never work for a foreignkey

6

This is only an issue when you’re using to_field instead of the default of the target’s primary key. (A PK cannot be null.)

The to_field satisfies a different set of requirements - specifically including those where null is a valid option for an otherwise-unique field.

If you must rely upon a not-null situation, then use the primary key for the relationship.