0

In addition to a data type of VARCHAR(255), I'd like to add a constraint to the data by a regular expression, e.g. [a-zA-Z ].

I'd like to be able to query the regex, like how I can query meta data in the information_schema.

I've seen some related posts, but not exactly what I'm looking for. I've seen: Restrict varchar() column to specific values? which shows how to use constaints, and I've seen via triggers here: Is it Possible to Enforce Data Checking in MySQL using Regular expression

Can RegExs be used as constraints and those constraints be queried? If so, how?

Community
  • 1
  • 1
James Oravec
  • 19,579
  • 27
  • 94
  • 160
  • I got a hackish way of doing it, which would be to put the regex in the column comments then use a trigger to check before insert. I would prefer a cleaner way of doing this. – James Oravec Feb 01 '16 at 23:55
  • Not everything can be done in SQL. This smells like something for the application to do, not a `TRIGGER`. – Rick James Feb 02 '16 at 00:53
  • True, that is how I've done it in the past, however since the DBE is the handler of the data, I think it makes sense that it helps enforce things. The system I'm working on is also n-tier, so have it controlled at the db level and allowing for the meta data to be queried would allow me to enforce it on various levels. Otherwise its a coordination effort that ends in a lot of work. Going to do a new post to see if any other db engine can do this. – James Oravec Feb 02 '16 at 15:31
  • The following links to the other post I made that opens the question up to all db engines: http://stackoverflow.com/questions/35157662/is-there-a-database-engine-that-allows-for-queriable-field-constraint-specified – James Oravec Feb 02 '16 at 15:42

0 Answers0