Restrict varchar() to range a-z

Question

I'm working on implementing and designing my first database and have a lot of columns with names and addresses and the like.

It seems logical to place a CHECK constraint on these columns so that the DB only accepts values from an alphanumeric range (disallowing any special characters).

I am using MySQL which, as far as I can tell doesn't support user defined types, is there an easy way to do this?

It seems worth while to prevent bad data from entering the DB, but should this complex checking be offloaded to the application instead?

Its a good practice to do all such error checking and constraints implemented at your application end. — Shiridish, Dec 08 '12 at 12:34
@Cdeez I disagree. Best practice is to move any checking that can be done in the database to the database. It maintains better consistency especially if your DB is supporting multiple applications. In this situation you might move this to the application end because the DB doesnt handle it well, but it makes maintaining data integrity harder and should be avoided if you can. — gbtimmon, Dec 08 '12 at 12:52
@Cdeez: no it's not. Although with MySQL's limited capabilities it in fact is - with every other DBMS creating a CHECK constraint would do this much more efficiently and reliably. — , Dec 08 '12 at 12:59
This is good to know. I've read that having it in the DBMS makes the most sense. Otherwise, as said you'd need to move the logic into every app that uses the DB. — james_dean, Dec 08 '12 at 13:03

score 1 · Accepted Answer · answered Dec 08 '12 at 12:46

You can't do it with a CHECK constraint if you're using mysql (question is tagged wth mysql, so I presume this is the case) - mysql doesn't support check constraints. They are allowed in the syntax (to be compatible with DDL from other databases), but are otherwise ignored.

You could add a trigger to the table that fires on insert and update, that checks the data for compliance, but if you find a problem there's no way to raise an exception from a mysql stored proc.

I have used a workaround of hitting a table that doesn't exist, but has a name that conveys the meaning you want, eg

update invalid_characters set col1 = 1;

and hope that the person reading the "table invalid_characters does not exist" message gets the idea.

Thanks for the tip. I am indeed using MySQL. – james_dean Dec 08 '12 at 12:49 — james_dean, Dec 08 '12 at 12:49

score 0 · Answer 2 · edited May 23 '17 at 10:24

There are several settings that allows you to change how MySQL handles certain situation (but those aren't enough) for your case.

I would stick with data validation on application side but if you need validation on database side, you have two options:

CREATE PROCEDURE that would validate and insert data, do nothing or raise error by calling SIGNAL
CREATE TRIGGER ... BEFORE INSERT which would validate data and stop insert like suggested in this stackoverflow answer

Restrict varchar() to range a-z

2 Answers2