table unqiueness constraints on assignmentid, and workerid

[jacob-lee]

In the Participant table, we have uniqueid as the primary key, but this in practice seems to be the combination: uniqueid == [assignmentid]:[workerid].

Technically, we don't need uniqueid, because the combination of assignmentid and workerid implicitly define a composite primary key. But, since we aren't ever updating any of those three columns it isn't that big a deal to have that functional dependency/redundancy.

Still, it seems like we should specify a joint uniqueness constraint on those two columns.

class Participant(Base):
    """
    Object representation of a participant in the database.
    """
    __tablename__ = TABLENAME

    uniqueid = Column(String(128), primary_key=True)
    assignmentid = Column(String(128), nullable=False)
    workerid = Column(String(128), nullable=False)
    hitid = Column(String(128), nullable=False)
    ...

class Participant(Base):
    """
    Object representation of a participant in the database.
    """
    __tablename__ = TABLENAME
    __table_args__ = (
        sqlalchemy.schema.UniqueConstraint(
            'assignmentid', 'workerid', name='worker_assignment_unique_constraint')
    )

    uniqueid = Column(String(128), primary_key=True)
    assignmentid = Column(String(128), nullable=False)
    workerid = Column(String(128), nullable=False)
    hitid = Column(String(128), nullable=False)
    ...

Any reason not to?

[deargle]

We would want to check whether sqlalchemy will vomit if it finds an already existing _tablename_ that doesn't have those constraints. Sqlalchemy doesn't handle migrations, but if I remember the name right, Amnesic does. I looked into that when we added the mode column, but I never implemented a migration script.

…

On Mon, Oct 26, 2020, 1:18 PM jacob-lee ***@***.***> wrote: In the Participant table, we have uniqueid as the primary key, but this in practice seems to be the combination: uniqueid == [assignmentid]:[workerid]. Technically, we don't need uniqueid, because the combination of assignmentid and workerid implicitly define a composite primary key. But, since we aren't ever updating any of those three columns it isn't that big a deal to have that functional dependency/redundancy. Still, it seems like we should specify a joint uniqueness constraint on those two columns. class Participant(Base): """ Object representation of a participant in the database. """ __tablename__ = TABLENAME uniqueid = Column(String(128), primary_key=True) assignmentid = Column(String(128), nullable=False) workerid = Column(String(128), nullable=False) hitid = Column(String(128), nullable=False) ... class Participant(Base): """ Object representation of a participant in the database. """ __tablename__ = TABLENAME __table_args__ = ( sqlalchemy.schema.UniqueConstraint( 'assignmentid', 'workerid', name='worker_assignment_unique_constraint') ) uniqueid = Column(String(128), primary_key=True) assignmentid = Column(String(128), nullable=False) workerid = Column(String(128), nullable=False) hitid = Column(String(128), nullable=False) ... Any reason not to? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#450>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAI6Y7JY6U6ZXL2OMKPPBDLSMXDRRANCNFSM4S7ZZIYA> .