Django multi-table inheritance - make sure only one child exists (CheckConstraint)

Question

How can I make sure that a parent object has only one child/type?

class Property(...):
    class Meta:
        abstract = False

class Flat(Property):
    pass

class House(Property):
    pass 

class Land(Property):
    pass

I want every property object to have none or at most one child. It can be either flat, house or land (or null).

Is it possible to create a DB constraint for this?

My idea was to create a constraint that checks:

class Meta:
    constraints = [
        models.CheckConstraint(check=Q(Q(flat__isnull=True) & Q(house__isnull=True)) 
                                     | 
                                     Q(Q(flat__isnull=True) & Q(land__isnull=True)) 
                                     |
                                     Q(Q(house__isnull=True) & Q(land__isnull=True)), 
                                name="constraint")]

But apparently, there are no such fields on a DB level (you can get flat by property.flat getter in Django but not in DB)

Edit:

properties.Property: (models.E012) 'constraints' refers to the nonexistent field 'flat'.

You can't create a database constraint for this, since a `CHECK` constraint only spans a *row* for *one* table, so you can not make JOINs on other tables. — Willem Van Onsem, Jun 24 '22 at 20:12

score 1 · Accepted Answer · answered Jun 26 '22 at 06:18

But apparently, there are no such fields on a DB level (you can get flat by property.flat getter in Django but not in DB)

That is correct: Django adds a property to the Property model to lazily load the related Flat object and will make a query for that, but there is no database field named flat: this is just a query in reverse where Django basically queries with:

SELECT * FROM app_name_flat WHERE property_ptr=pk

with pk the primary key of the property object. It this makes a query.

A CHECK constraint ^[w3-schools] spans only over a row: it can not look on other rows nor can it look at other tables. It thus can not restrict other tables, and therefore is limited. It can for example prevent one column to have a certain value based on a value for another column in the same row (record), but that is how far a CHECK constraint normally looks.

Django multi-table inheritance - make sure only one child exists (CheckConstraint)

Edit:

1 Answers1