sqlalchemy.exc.ProgrammingError: (ProgrammingError) there is no unique constraint matching given keys for referenced table

Question

I am using sqlalchemy to create two tables in the following:

class Classroom(Base):
    __tablename__ = 'classroom'
    building = Column(String(256), primary_key = True)
    room_no = Column(String(256), primary_key = True)
    capacity = Column(Integer)

class Section(Base):
    __tablename__ = 'section'
    course_id = Column(String(256), ForeignKey('course.course_id'), primary_key = True)
    building = Column(String(256), ForeignKey('classroom.building'))
    room_no = Column(String(256), ForeignKey('classroom.room_no'))

However, I got the error saying:

 sqlalchemy.exc.ProgrammingError: (ProgrammingError) there is no unique constraint matching given keys for referenced table "classroom"

Craig Ringer · Accepted Answer · 2014-01-26 12:12:03Z

16

I just figured out, in this case I need to have a multicolumn foreig key constraints: Changed my section model to be:

class Section(Base):
    __tablename__ = 'section'
    course_id = Column(String(256), ForeignKey('course.course_id'), primary_key = True)
    building = Column(String(256))
    room_no = Column(String(256))
    __table_args__ = (ForeignKeyConstraint([building, room_no],[Classroom.building, Classroom.room_no]), {})

and everything works.

edited Jan 26, 2014 at 12:12

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered Jan 26, 2014 at 11:45

hashchen

1,0593 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

Thanks for following up when you figured it out, rather than just abandoning the question.

Collectives™ on Stack Overflow

sqlalchemy.exc.ProgrammingError: (ProgrammingError) there is no unique constraint matching given keys for referenced table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related