Creating a model with two optional, but one mandatory foreign key

My problem is that I have a model that can take one of two foreign keys to say what kind of model it is. I want it to take at least one but not both. Can I have this still be one model or should I split it into two types. Here is the code:

class Inspection(models.Model):
    InspectionID = models.AutoField(primary_key=True, unique=True)
    GroupID = models.ForeignKey('PartGroup', on_delete=models.CASCADE, null=True, unique=True)
    SiteID = models.ForeignKey('Site', on_delete=models.CASCADE, null=True, unique=True)

    @classmethod
    def create(cls, groupid, siteid):
        inspection = cls(GroupID = groupid, SiteID = siteid)
        return inspection

    def __str__(self):
        return str(self.InspectionID)

class InspectionReport(models.Model):
    ReportID = models.AutoField(primary_key=True, unique=True)
    InspectionID = models.ForeignKey('Inspection', on_delete=models.CASCADE, null=True)
    Date = models.DateField(auto_now=False, auto_now_add=False, null=True)
    Comment = models.CharField(max_length=255, blank=True)
    Signature = models.CharField(max_length=255, blank=True)

The problem is the Inspection model. This should be linked to either a group or a site, but not both. Currently with this set up it needs both.

I'd rather not have to split this up into two nearly identical models GroupInspection and SiteInspection, so any solution that keeps it as one model would be ideal.

I would suggest that you do such validation the Django way

by overriding the clean method of Django Model

class Inspection(models.Model):
    ...

    def clean(self):
        if <<<your condition>>>:
            raise ValidationError({
                    '<<<field_name>>>': _('Reason for validation error...etc'),
                })
        ...
    ...

Note, however, that like Model.full_clean(), a model’s clean() method is not invoked when you call your model’s save() method. it needs to be called manually to validate model's data, or you can override model's save method to make it always call the clean() method before triggering the Model class save method


Another solution that might help is using GenericRelations, in order to provide a polymorphic field that relates with more than one table, but it can be the case if these tables/objects can be used interchangeably in the system design from the first place.

Chapter 6. Entity-Relationship Modelling, Entity representation; One-to-one relationships between two entities Weak and strong entities; Problems with entity-relationship (ER) models Mandatory for both entities; Mandatory for one entity, optional for the other entity; Optional entity, the copy of the primary key held in the second entity is known as a foreign key. The ForeignKey attribute is used to specify which property is the foreign key in a relationship. It is used to express the relationship between two tables. The default code first convention for ForeignKey relationship expects foreign key property name match with the primary key property.

Mandatory Relationship, The non-key attribute means it is a foreign key pointing back to the parent entity. The first order for customer 0985 (Order #1) contains only one item (item 02944 ). The line item entity has been created solely to represent the relationship between an Optional Relationship: An optional relationship between two features� Create a foreign key relationship in Table Designer Using SQL Server Management Studio. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design. The table opens in Table Designer. From the Table Designer menu, click Relationships. In the Foreign-key Relationships dialog box, click Add.

Django has a new (since 2.2) interface for creating DB constraints: https://docs.djangoproject.com/en/3.0/ref/models/constraints/

You can use a CheckConstraint to enforce one-and-only-one is non-null. I use two for clarity:

class Inspection(models.Model):
    InspectionID = models.AutoField(primary_key=True, unique=True)
    GroupID = models.OneToOneField('PartGroup', on_delete=models.CASCADE, blank=True, null=True)
    SiteID = models.OneToOneField('Site', on_delete=models.CASCADE, blank=True, null=True)

    class Meta:
        constraints = [
            models.CheckConstraint(
                check=~Q(SiteID=None) | ~Q(GroupId=None),
                name='at_least_1_non_null'),
            ),
            models.CheckConstraint(
                check=Q(SiteID=None) | Q(GroupId=None),
                name='at_least_1_null'),
            ),
        ]

This will only enforce the constraint at the DB level. You will need to validate inputs in your forms or serializers manually.

As a side note, you should probably use OneToOneField instead of ForeignKey(unique=True). You'll also want blank=True.

Models — Django 3.1 documentation, The above Person model would create a database table like this: If you'd like to specify a custom primary key, specify primary_key=True on one of your Each field type, except for ForeignKey , ManyToManyField and OneToOneField , takes an optional It's suggested, but not required, that the name of a ForeignKey field � One-to-Many Optional to Mandatory becomes a _____ on the Master table. Mark for Review (1) Points Primary Key Optional Foreign Key (*) Mandatory Foreign Key Unique Key Correct Correct Page 1 of 3 Next Summary Test: Section 9 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer. Section 9 Quiz

I think you're talking about Generic relations, docs. Your answer looks similar to this one.

Sometime ago I needed to use Generic relations but I read in a book and somewhere else that the usage should be avoided, I think it was Two Scoops of Django.

I ended up creating a model like this:

class GroupInspection(models.Model):
    InspectionID = models.ForeignKey..
    GroupID = models.ForeignKey..

class SiteInspection(models.Model):
    InspectionID = models.ForeignKey..
    SiteID = models.ForeignKey..

I‘m not sure if it is a good solution and as you mentioned you'd rather not use it, but this is worked in my case.

Models and Fields — peewee 3.13.3 documentation, When creating a Model class, fields are defined as class attributes. There is one special type of field, ForeignKeyField , which allows you to represent ( SQLite-only); choices = None – optional iterable containing 2-tuples of value , Typically a foreign key will contain the primary key of the model it relates to (but you can� The second problem is the one-to-one relationship between Persons and Users. If you just had a Persons table, then the question of which way the foreign keys should go is driven by the fact that the relationship is mandatory at one end, but optional at the other end. If you put PersonID in the Users table, you won't have to use any NULLs here.

It might be late to answer your question, but I thought my solution might fit to some other person's case.

I would create a new model, let's call it Dependency, and apply the logic in that model.

class Dependency(models.Model):
    Group = models.ForeignKey('PartGroup', on_delete=models.CASCADE, null=True, unique=True)
    Site = models.ForeignKey('Site', on_delete=models.CASCADE, null=True, unique=True)

Then I would write the logic to be applicable very explicitly.

class Dependency(models.Model):
    group = models.ForeignKey('PartGroup', on_delete=models.CASCADE, null=True, unique=True)
    site = models.ForeignKey('Site', on_delete=models.CASCADE, null=True, unique=True)

    _is_from_custom_logic = False

    @classmethod
    def create_dependency_object(cls, group=None, site=None):
        # you can apply any conditions here and prioritize the provided args
        cls._is_from_custom_logic = True
        if group:
            _new = cls.objects.create(group=group)
        elif site:
            _new = cls.objects.create(site=site)
        else:
            raise ValueError('')
        return _new

    def save(self, *args, **kwargs):
        if not self._is_from_custom_logic:
            raise Exception('')
        return super().save(*args, **kwargs)

Now you just need to create a single ForeignKey to your Inspection model.

In your view functions, you need to create a Dependency object and then assign it to your Inspection record. Make sure that you use create_dependency_object in your view functions.

This pretty much makes your code explicit and bug proof. The enforcement can be bypassed too very easily. But the point is that it needs prior knowledge to this exact limitation to be bypassed.

Model field reference | Django documentation, Technically, these models are defined in django.db.models.fields , but for convenience If a string-based field has null=True , that means it has two possible values for “no data”: NULL table with a ForeignKey . choices is meant for static data that doesn't change much, if ever. CharField has one extra required argument:. Note, that when working with 1-to-1 or 1-to-0..1 relationships, there is no separate foreign key column, the primary key property acts as the foreign key and is always included in the model. When foreign key columns are not included in the model, the association information is managed as an independent object.

Defining primary key–foreign key relationships, Columns that define primary keys in one table in a relational model can have a primary key ensure uniqueness in the table; no two rows can have the same key. table will not be added to its primary key, and that the relationship is optional. Diagram Editor, you create these primary key-foreign key relationships simply� The disadvantage of a nullable foreign key is being unable to model the relationship as many-to-many, if that is what you are trying to accomplish. Based on your edit to the question, you are effectively splitting the student table into two tables with the same key.

CONSTRAINT clause, A CONSTRAINT clause is an optional part of a CREATE TABLE statement or Note: If the foreign key consists of multiple columns, and any column is NULL, the Column-level constraints (except for check constraints) refer to only one column. foreign key constraint in which you specify the columns in the table that make� In the case of one-to-one relationships, the creation of one or two relations is sufficient, depending on whether participation is mandatory or optional. Mandatory for both entities A single relation will be able to represent the information represented by each entity and the relationship that exists between them.

Mapping ER Models into Relations, tablename(primary key, attribute 1, attribute 2, , foreign key) way around it is done but you should not have a foreign key in each entity. It is better NOT to subsume the mandatory end into the optional end as this will create null entries.

Comments
  • Perhaps using subclassing is better here. You can make an Inspection class, and then subclass into SiteInspection and GroupInspection for the non-common parts.
  • Possibly unrelated, but the unique=True part in your FK fields means that only one Inspection instance can exist for one given GroupID or SiteID instance - IOW, it's a one to one relationship, not a one to many. Is this really what you want ?
  • "Currently with this set up it needs both." => technically, it doesn't - at the database level, you can either set both, either or none of those keys (with the caveat mentionned above). It's only when using a ModelForm (directly or via django admin) that those fields will be marked as required, and that's because you didn't pass the 'blank=True' argument.
  • @brunodesthuilliers Yes the idea is to have Inspection be a link between the Group or Site and an InspectionID, then I can have multiple "inspections" in the form of InspectionReport for that one relationship. This was done so that I can more easily sort by Date for all records related to one Group or Site. Hope that makes sense
  • @Cm0295 I'm afraid don't see the point of this indirection level - putting the group / site FKs directly into InspectionReport yields the exact same service AFAICT - filter your InspectionReports by the appropriate key (or just follow the reverse descriptor from Site or Group), sort them by date and you're done.
  • "I read in a book and somewhere else" is about the worse possible reason to do (or avoid doing) something.
  • @brunodesthuilliers I thought Two Scoops of Django was a good book.
  • Can't tell, I haven't read it. But that's unrelated: my point is that if you don't understand why the book says so, then it's not knowledge nor experience, it's religious belief. I don't mind religious belief when it comes to religion, but they have no place in CS. Either you understand what are the pros and cons of some feature and then you can judge whether it's appropriate in a given context, or you don't and then you should not mindlessly parrot what you've read. There are very valid use case for generic relations, the point is not to avoid them at all but to know when to avoid them.
  • NB I perfectly understand that one cannot know everything about CS - there are domains where I have no other options than trusting some book. But then I will probably not answer questions on that topic ;-)