Alembic: Enforce value uniqueness across two values

Question

I have the following table

mysql> describe table;
+----------------+-------------+------+-----+-------------------+----------------+
| Field          | Type        | Null | Key | Default           | Extra          |
+----------------+-------------+------+-----+-------------------+----------------+
| id             | int(11)     | NO   | PRI | NULL              | auto_increment |
| value1         | varchar(2)  | NO   | MUL | NULL              |                |
| value2         | varchar(2)  | YES  |     | NULL              |                |
| value3         | varchar(10) | YES  |     | NULL              |                |
+----------------+-------------+------+-----+-------------------+----------------+

I'm trying to create an alembic rule, where value1 and value2 together create a key in the table. For example, the values

(id=1, value1="BA", value2="CN", value3="hello")
(id=2, value1="BA", value2="CN", value3="goodbye")

are the same (value1 and value2 match), but

(id=1, value1="BA", value2="CN", value3="hello")
(id=2, value1="BA", value2="US", value3="goodbye")

are not.

What would be the alembic upgrade() and downgrade() code for this be, using an alembic.op object?

score 0 · Answer 1 · answered Nov 12 '16 at 20:36

Assuming that your model has the definition of the following UniqueConstraint:

class MyTable(Base):
    __tablename__ = 'mytable'

    id = Column(Integer, primary_key=True)
    value1 = Column(String(2), nullable=False)
    value2 = Column(String(2))
    value3 = Column(String(10))

    __table_args__ = (
        UniqueConstraint(value1, value2, name="mytable__uc_value1_value2"),
    )

The def upgrade(): of alembic revision would then contain following command in order to create this unique constraint:

def upgrade():
    op.create_unique_constraint(
        "mytable__uc_value1_value2", "mytable", ["value1", "value2"]
    )

score 0 · Answer 2 · answered Mar 25 '22 at 16:14

0

thanks van for the answer to upgrade. I also add the code to downgrade:

from alembic import op


def upgrade():
    op.create_unique_constraint(
        "uc_table_name_column1_column2_column3", "table_name",  
         ["column1", "column2", "column3"], "schema_name"
    )

def downgrade():
    op.drop_constraint(
      "uc_table_name_column1_column2_column3", 
      "table_name", "unique", "schema_name"
    )

answered Mar 25 '22 at 16:14

Erdem Taskin

1
1

Please don't add "thank you" as an answer. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation), you will be able to [vote up questions and answers](https://stackoverflow.com/help/privileges/vote-up) that you found helpful. - [From Review](/review/late-answers/31376955) – player0 Mar 31 '22 at 12:54

Alembic: Enforce value uniqueness across two values

2 Answers2