How to specify uniqueness across a relationship in Django

Question

I'm trying to represent some movie ratings data in Django. Here is a simplified version of my models that illustrates my problem:

class RatingSystem(models.Model):
    """This denotes a rating authority and territory in which they operate"""
    name = models.CharField(max_length=16)
    territory = models.CharField(max_length=32)

class Rating(models.Model):
    """This represents a rating designation used by a rating system."""
    code = models.CharField(max_length=16)
    description = models.TextField()
    system = models.ForeignKey(RatingSystem)

class FilmRating(models.Model):
    """This is a rating for a film and the reason why it received the rating.

    Each film can have many ratings, but only one per rating system.
    """
    rating = models.ForeignKey(Rating)
    film = models.ForeignKey('Film')
    reason = models.TextField()

class Film(models.Model):
    """Data for a film."""
    title = models.CharField(max_length=64)
    synopsis = models.TextField()
    ratings = models.ManyToManyField(Rating, through=FilmRating)

As the comments indicate, each Film can have multiple ratings, but only one rating per rating system. For instance, a film cannot be rated both 'R' and 'PG' by the MPAA. However, it can be rated 'R' by the MPAA and '15' by the BBFC.

I'm struggling to formalize this constraint in Django. I'd like to do:

unique_together = ('film', 'rating__system')

in FilmRating but following a relationship like that doesn't seem to be allowed. If I were using pure SQL, I would make code and system a composite primary key in Rating, then make a unique constraint on system, and film in FilmRatings. Unfortunately, Django does not support composite keys. I've considered overriding the save() method of FilmRating, but I'd prefer to have the constraint at the database level if possible.

Anyone have any idea how to do this? Restructuring the tables would be fine too if it would help.

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

I think you should take a look at validate_unique

This same problem some years ago in stackoverflow

The Django Docs

edited May 23 '17 at 11:56

Community

1
1

answered Nov 07 '12 at 19:13

esauro

1,276
10
17

The validate_unique() method seems to be a reasonable place to handle this. My problem with it is that it is not called automatically; it requires the programmer to run full_clean() before saving. I really want the constraint built into the database so that data integrity is guaranteed even if the db is modified outside of Django. My goal may not be possible using Django models, unless someone can think of another way. – M Saavedra Nov 07 '12 at 20:08

Arion · Accepted Answer · 2012-11-08T18:09:31.190

2

EDIT: Updated answer based on @JoshSmeaton's and @MSaavedra's comments

Using Django's syncdb hook, you could run the ALTER TABLE statements directly on the database. Django will raise an IntegrityError if the unique constraint is violated, even though that constraint wasn't defined by django.

Then, adding the constraint to validate_unique would reduce developer confusion later on and safely enforce the constraint in Django.

edited Nov 08 '12 at 18:09

answered Nov 08 '12 at 03:03

Arion

1,792
2
18
29

2

I think adding the db constraint is a good idea as a last defence, but using the `validate_unique` method would clear up any misunderstandings a developer might have in django. – Josh Smeaton Nov 08 '12 at 03:12
2

Perhaps running alter table statements inside `sql/.sql` files would be the best way to handle the SQL, so that it would be run automatically if the database is recreated. You and @JoshSmeaton are correct that having validation within Django is necessary, and leave the raw SQL constraints just as a last line of defense. – M Saavedra Nov 08 '12 at 17:25
@MSaavedra, that's really useful! I didn't know about the sql-specific syncdb hooks. – Arion Nov 08 '12 at 17:58

score 1 · Answer 3 · answered Nov 07 '12 at 19:23

1

You can use model field validation.

https://docs.djangoproject.com/en/dev/ref/models/instances/#validating-objects

answered Nov 07 '12 at 19:23

dannyroa

5,501
6
41
59

As I mentioned in another comment, I really want the constraint built into the database so that data integrity is guaranteed if the programmer doesn't call full_clean() before saving, or even if the db is modified outside of Django. – M Saavedra Nov 07 '12 at 20:09

How to specify uniqueness across a relationship in Django

3 Answers3