Use Regex in a 'CheckConstraint' from SQLAlchemy

Question

Considering the example below, how can I make the constraint work according to the given regex?

In this case, I am using SQLAlchemy inside a Flask application.

class user(db.Model):
    iduser = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(45), unique=True)
    CheckConstraint("REGEXP_LIKE(email,'^[a-zA-Z][a-zA-Z0-9_\.\-]+@([a-zA-Z0-9-]{2,}\.)+([a-zA-Z]{2,4}|[a-zA-Z]{2}\.[a-zA-Z]{2})$')", name='emailcheck')

(I am not 100% sure about the syntax in the last line)

univerio · Accepted Answer · 2017-06-28 00:50:15Z

6

CHECK constraints need to be in the table itself, using __table_args__:

class user(db.Model):
    ...
    __table_args__ = (CheckConstraint("regexp_like(email, ...)", name=...),)

You can also put it outside of the class, but SQLAlchemy needs to know what table it's for, so you'll need to write the constraint as an expression instead of a string:

class user(db.Model):
    ...

CheckConstraint(func.regexp_like(user.email, ...), name=...)

edited Jun 28, 2017 at 0:50

answered Jun 27, 2017 at 23:30

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Auyer Over a year ago

I didn't work. It had no effect on the database at all. I should note that I am not exactly sure about this syntax where "regexp_like(email, ...)" is all sent as a string... I used it as an example.

univerio Over a year ago

@RafaelAuyer What do you mean it had no effect? How are you verifying it has no effect?

Auyer Over a year ago

I figured that I needed to recreate the table for it to take effect.

Rayhan Muktader Over a year ago

Does this not work with an existing table?

baxx Over a year ago

This didn't work for me with an existing table - i added the check constraint as above, regenerated migrations, then viewed table in dbeaver and no constraints were added (though comments were updated). Using postgres 14, alembic, sqlalchemy.

|

Collectives™ on Stack Overflow

Use Regex in a 'CheckConstraint' from SQLAlchemy

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related