Alembic: Include constraint to reject strings with character

Question

I have a table that looks like this

> select * from mytable
  id        value
0  1  hello world
1  2  hello_world
2  3  hello+world

I'm trying to impose an alembic check constraint, where values in value cannot have the : character. How would I do this using an alembic.op object? What are the upgrade() and downgrade() functions?

Edit: The DB I'm using is Mysql.

How would this constraint be applied? On new data or on existing data somehow? What should be done if existing data violates said constraint? Adding constraints can also be – and is – a bit DB specific. What DB are you using? — Ilja Everilä, Nov 11 '16 at 11:17
@IljaEverilä All true. The assumption is that there is some data in the table, but they don't violate the constraint. The DB I'm using is Mysql. — hlin117, Nov 11 '16 at 22:20

score 6 · Answer 1 · answered Nov 11 '16 at 22:19

In this case, a check constraint works

def upgrade():
    op.create_check_constraint("constraint_name_here", "mytable", "value not like '%:%'")

def downgrade():
    op.drop_constraint("constraint_name_here", "mytable", type_="check")

But mysql doesn't support check constraints. ¯_(ツ)_/¯

Alembic: Include constraint to reject strings with character

1 Answers1