'Alembic: Enforce value uniqueness across two values

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?

mysqlsqlalchemyalembic


Solution 1:[1]

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"]
    )

Solution 2:[2]

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"
    )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 van
Solution 2 Erdem Taskin

Related Questions