Querying issue with ForeignKeys where db_constraint=False

[Alex Bailey]

models.py

from django.db import models

class Person(models.Model):
    name = models.CharField(max_length=25)

class Book(models.Model):
    foo = models.ForeignKey(Person, db_constraint=False, on_delete=models.DO_NOTHING, related_name='foo')
    bar = models.ForeignKey(Person, db_constraint=False, on_delete=models.DO_NOTHING, related_name='bar')

setup

from .models import Book, Person

alex = Person.objects.create(name='Alex')

Book.objects.create(foo=alex, bar=alex)
Book.objects.create(foo=alex, bar_id=999)
Book.objects.create(foo_id=999, bar=alex)

shell

>>> from django.db.models import Q

#Correct: Returns both books where foo__name == 'Alex'
>>> Book.objects.filter(foo__name='Alex') 
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>

#Correct: Returns both books where bar__name == 'Alex'
>>> Book.objects.filter(bar__name='Alex')
<QuerySet [<Book: Book object (1)>, <Book: Book object (3)>]>

#Only returns one book, expected to return all 3. 
>>> Book.objects.filter(Q(foo__name='Alex') | Q(bar__name='Alex'))
<QuerySet [<Book: Book object (1)>]>

I am working with an existing DB where it is possible for a Person to no longer exist, hence the lack of db_constraint in the ForeignKey.

Books can refer to non-existent Persons but it still seems like expected behavior would be for the queryset OR to return all 3 books. I believe this is coming from the Django ORM doing an INNER JOIN. The 3rd query should be a simple union of the first 2 queries.

All 3 books fit into the expected query of books WHERE foo__name = 'Alex' OR bar__name = 'Alex' but the Django behavior does not reflect this.

[Alex Bailey]

I just noticed that adding null=True to the ForeignKeys produces the expected result on the last query:

class Book(models.Model):
    foo = models.ForeignKey(Person, db_constraint=False, on_delete=models.DO_NOTHING, related_name='foo', null=True)
    bar = models.ForeignKey(Person, db_constraint=False, on_delete=models.DO_NOTHING, related_name='bar', null=True)

>>> Book.objects.filter(Q(foo__name='Alex') | Q(bar__name='Alex'))
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>]>

Django switched to using LEFT OUTER JOIN in this case. Should this same logic apply to ForeignKeys where db_constraint=False?

[Simon Charette]

Closing as duplicate of #33608.

While Django allows for database constraints to be omitted it doesn't make any assumptions with regards to database integrity implications.

In other words, if you break integrity expectations of ForeignKey you must also adjust your code to do so and in your particular case if a Book is still allowed to exist without a person the proper way to represent that is by setting null=True on your field as you've discovered.

Adapting the ORM to special case db_constraint=False like it does with null=True has a few implications that ​should be debated on the mailing list before a decision is taken because ​it was not done when the change was merged.