Django unique_together on postgres: enforced by ORM or DB?

Question

As I look at the sqlall for a models.py that contains unique_together statements, I don't notice anything that looks like enforcement.

In my mind, I can imagine that this knowledge might help the database optimize a query, like so:

"I have already found a row with spam 42 and eggs 91, so in my search for eggs 91, I no longer need to check rows with spam 42."

Am I right that this knowledge can be helpful to the DB?

Am I right that it is not enforced this way (ie, it is only enforced by the ORM)?

If yes to both, is this a flaw?

Docs say it's enforced at the db level, would that be enough? :) — shanyu, Jul 10 '11 at 21:26
Hmm, can you link? I looked, but didn't see that. Even if so, I don't see where in the sqlall output the enforcement takes place. — jMyles, Jul 10 '11 at 21:39
Here it is: https://docs.djangoproject.com/en/dev/ref/models/options/#django.db.models.Options.unique_together — shanyu, Jul 10 '11 at 21:44
OK, so the docs do say that. But what statement enforces it? — jMyles, Jul 11 '11 at 21:55

score 4 · Accepted Answer · answered Jul 26 '11 at 07:41

Here's an example how this should look. Assume that you have model:

class UserConnectionRequest(models.Model):
    sender = models.ForeignKey(UserProfile, related_name='sent_requests')
    recipient = models.ForeignKey(UserProfile, related_name='received_requests')
    connection_type = models.PositiveIntegerField(verbose_name=_(u'Connection type'), \
                                                  choices=UserConnectionType.choices())

    class Meta:
        unique_together = (("sender", "recipient", "connection_type"),)

Running sqlall it returns:

CREATE TABLE "users_userconnectionrequest" (
    "id" serial NOT NULL PRIMARY KEY,
    "sender_id" integer NOT NULL REFERENCES "users_userprofile" ("id") DEFERRABLE INITIALLY DEFERRED,
    "recipient_id" integer NOT NULL REFERENCES "users_userprofile" ("id") DEFERRABLE INITIALLY DEFERRED,
    "connection_type" integer,
    UNIQUE ("sender_id", "recipient_id", "connection_type")
)

When this model is properly synced on DB it has unique constraint (postgres):

CONSTRAINT users_userconnectionrequest_sender_id_2eec26867fa22bfa_uniq UNIQUE (sender_id, recipient_id, connection_type),

Django unique_together on postgres: enforced by ORM or DB?

1 Answers1