Unique constraint on non-primary key fields of a many-to-many relationship

[Joe Jean]

Hi All,

I'm using flask-sqlalchemy. I have a many-to-many relationship between a professor table and a course table as follow:

course_professors = db.Table('course_professors',
                         db.Column('course_id', db.Integer, db.ForeignKey('course.id')),
                         db.Column('professor_id', db.Integer, db.ForeignKey('professor.id'))


                      )

class Professor(db.Model):
id = db.Column(db.Integer, primary_key = True )
professor_name = db.Column (db.String(80), unique= True, nullable = False, index = True)
courses = db.relationship('Course', secondary = course_professors , backref = db.backref('professor', collection_class=set),\
 collection_class=set)


class Course(db.Model):
    id = db.Column(db.Integer, primary_key = True )
    course_name =db.Column(db.String(120), index = True, nullable = False)
    course_description = db.Column(db.Text, nullable = False)

What I want is that the combination (course_name, professor_name) be unique in the database, in order words I want to prevent (course_name, professor_name) duplicates. Note that it is perfectly fine if the same course name appears more than once in the table, but I just don't want the combination (course_name, professor_name) to appear more than once. I have vainly tried a couple of ways to fix this. For example I have used collection_class=set on both ends of the relationship. Please note that applying a Unique key constraint as:

(UniquekeyConstraint(course_id, professor_id))

in the association table won't work either because two courses that have the same name don't have the same id.So what I want is a way to apply the unique constraint on the course name and professor name. Can somebody please help me with that ? Thank you.

[Michael Bayer]

if you want to enforce that just on the Python side as you're doing, you either can make the Course object hash itself on the "name" field, so that using "set" will cause two Course objects of the same name to be considered the same, or better would probably be to map "Professor.courses" as an attribute_mapped_collection using course_name as the "key".   

This does mean though that if you put two Course objects with the same name in Professor.courses, it will just discard the other one, which is a little strange considering that two Courses can have the same name (and probably different descriptions).