CODEWITHSUNDEEP
mysqldatabaseconstraints
james_dean
james_dean

Reputation: 1517

Restrict varchar() to range a-z

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?

Upvotes: 0

Views: 1060

Answers (2)

Vyktor
Vyktor

Reputation: 21007

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:

Upvotes: 0

Bohemian
Bohemian

Reputation: 425083

You can't do it with a CHECK constraint if you're using mysql (question is tagged wth , 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.

Upvotes: 1

Related Questions