1

I have 2 models/tables, one a Project, and the other a Level. One project can have multiple levels, but each level can only belong to one Project. A level must have a floor (int), that needs to be unique for each project. In other words, (project.project_id, level.floor) must be unique. How do I enforce this type of constraint using SQLAlchemy?

I have removed all unnecessary fields from the 2 models:

class Project(Base):
    __tablename__ = "project"
    project_id = Column(Integer, primary_key=True)

class Level(Base):
    __tablename__ = "level"
    level_id = Column(Integer, primary_key=True)
    floor = Column(Integer)
    project_id = Column(Integer, ForeignKey("project.project_id"))
    project = relationship(
        "Project",
        backref="levels",  #! might see an error here (project does not exist)
        cascade="all, delete",
    )
    UniqueConstraint("project_id", "floor", name="project_level_floor")

UPDATE:

This appears to a SQLite issue, as I have the exact same code in a different app that uses Postgres and that enforces this unique constraint.

user1314147
  • 174
  • 1
  • 5
  • 25
  • Can't use the unique=True in the column? – Paul Aug 17 '20 at 22:57
  • So I can put unique in any column, but I want the combination of them to be unique (so composite key?) – user1314147 Aug 17 '20 at 23:05
  • The `UniqueConstraint` *in the class body* is essentially a no-operation: https://stackoverflow.com/questions/10059345/sqlalchemy-unique-across-multiple-columns, https://stackoverflow.com/questions/26895207/how-is-a-unique-constraint-across-three-columns-defined/26897763#26897763, https://stackoverflow.com/questions/16460725/sqlalchemy-not-producing-proper-sql-statement-for-multi-column-uniqueconstraints/16460795#16460795, https://stackoverflow.com/questions/43275547/slqlalchemy-uniqueconstraint-vs-indexunique-true/43275972#43275972, it belongs to `__table_args__`. – Ilja Everilä Aug 21 '20 at 11:39

0 Answers0