Geek asks and answers

Regular expressions as part of SQL schema

Suppose I have a relational database in which, among other things, I want to store the names of employees and their identification strings. The format of the identification string is strictly defined: these are three lowercase alphabetical characters, then a dash, followed by a four-digit number.

Question: Does any relational database allow you to define a regular expression that must match a specific text field? As in my example, it would be nice to make the database check all employee identifier values ​​for a simple regular expression, and not at the user interface level.

Another question: if I have such problems (i.e. the need to check the field values ​​in comparison with an additional set of restrictions), does this mean that my circuit is denormalized, and I have to fix it?

+3
source share
3 answers

As for your second question, it depends. (Of course, it depends. It always depends.) If you always use your employee identification lines as a single integer value, then it is normalized. If you find that you are constantly breaking them into “first and second” parts (3 characters, 4 digits), you break the first normal form. (Roughly speaking, you have two facts in one column and they must break them down into their own columns.)

, , , , . , , ? RDBMS , , . , . , , .

0

, .

, . , .

+2

. . SQL Server , :

check (len(EmpId) = 7 and left(EmpId, 3) between 'AAA' and 'ZZZ' and
       substring(EmpId, 4) = '-' and isnumeric(right(EmpId, 4)) = 1

-, , CONSTRAINT.

-, . .

-, . , "B1B" . .

, , , , .

0
source

More articles:


All Articles