CODEWITHSUNDEEP
pythondjangopostgresqldjango-models
Davy Kavanagh
Davy Kavanagh

Reputation: 4949

Composite Foreign Key in Django

I need a Composite Foreign Key in Django, which isn't supported. I could add it manually to the DB, or through migrations but then it won't be reflected in the model definition (sadpanda). The backend DB is postgres.

Here's my models:

class Trial(models.Model):
    kit = models.ForeignKey(to='Kit')

class Kit(models.Model):
    name = models.CharField(max_length=500)


class Component(models.Model):
    kit = models.ForeignKey(null=True, blank=True, to='Kit', related_name='components')


class ComponentOverride(models.Model):
    trial = models.ForeignKey(to='Trial')
    kit = models.ForeignKey(to='Kit')
    component_to_replace = models.ForeignKey(to='Component', related_name='replaced')
    component_replace_with = models.ForeignKey(to='Component', related_name='replaced_with')

I want a foreign key constraint on the ComponentOverride table of the columns trial_id and kit_id (trial and kit in the models) to the id and kit id columns on the trial table (id is auto created by django, kit in the model is kit_id in the table).

Basically I want an equivalent to:

ALTER TABLE app_label_trial
    ADD CONSTRAINT app_label_trial_unique_trial_id_kit_id
    UNIQUE (id, kit_id);
ALTER TABLE app_label_componentoverride
    ADD CONSTRAINT app_label_componentoverride_comp_constraint_trial_id_kit_id
    FOREIGN KEY (kit_id, trial_id)
    REFERENCES app_label_trial(id, kit_id)
    DEFERRABLE INITIALLY DEFERRED;

I think I need the composite key because, there can be more than one component override per trial.

A trial has one kit, which has many components. However a trial may have one or more component overrides, which is essentially taking one component out of the kit and and replacing it with another. This schema explicitly captures the information of that replacement and is why I can't just use unique together. I want to ensure that every trial_id, kit_id combination in the componentoverride table is a valid combination in the trial table.

Upvotes: 5

Views: 6335

Answers (2)

Endre Both
Endre Both

Reputation: 5740

The easy solution for the case described would be to omit kit from ComponentOverride. If you refer to Trial from ComponentOverride, you have an implicit reference to the trial's kit via trial__kit_id. By additionally saving that kit_id in ComponentOverride you're needlessly duplicating data. By not saving the same data twice, the constraint problem goes away all by itself.

(This is an old question, but the catchy title is still directing some traffic to it, so maybe the above can save a few people from going down the rabbit hole of composite foreign keys when they don't need to.)

Upvotes: 2

Davy Kavanagh
Davy Kavanagh

Reputation: 4949

I ended up putting this at the end of my initial migrations:

migrations.RunSQL("""
            ALTER TABLE qc_trials_trial
                ADD CONSTRAINT qc_trials_trial_unique_trial_id_kit_id
                UNIQUE (id, kit_id);
            ALTER TABLE qc_trials_componentoverride
                ADD CONSTRAINT qc_trials_componentoverride_comp_constraint_trial_id_kit_id
                FOREIGN KEY (kit_id, trial_id)
                REFERENCES qc_trials_trial(id, kit_id)
                DEFERRABLE INITIALLY DEFERRED
"""),

If this was something I anticipated having to do a lot, I would write a custom operation for it. https://docs.djangoproject.com/en/1.10/ref/migration-operations/#writing-your-own

Upvotes: 1

Related Questions