Check constraint on multiple columns for BETWEEN

Question

CREATE TABLE item( id int, number1 float, number2 float, number3 float, number 4 float, PRIMARY KEY(id), 
CONSTRAINT valid_numbers CHECK(
(number1 BETWEEN -1 and 1) AND
(number2 BETWEEN -1 and 1) AND
(number3 BETWEEN -1 and 1) AND
(number4 BETWEEN -1 and 1)
));

The columns number1, number2, number3, and number4 are all similar because they are floats that need to be between -1 and 1; however they're values are different.

How would I simply this statement? For example, if it went all the way up to number15. I don't want to say "BETWEEN -1 and 1" 15 times, I'd like to say it only once.

MySQL also enforces `NOT NULL` constraints, and some inherent "constraints" for each datatype (INT, DATE, ENUM, etc.) — spencer7593, May 15 '15 at 19:08

score 0 · Answer 1 · answered May 15 '15 at 19:01

There's really no way to "simplify" this.

We could use an expression that avoids repeating BETWEEN -1 AND 1 multiple times, but it really isn't any simpler.

For example:

 GREATEST(ABS(IFNULL(number1,0))
         ,ABS(IFNULL(number2,0))
         ,ABS(IFNULL(number3,0))
         ,ABS(IFNULL(number4,0))
         ) <= 1

If any of the values in number1 through number4 are less than -1 or greater than 1, the expression will return FALSE.

But, MySQL doesn't enforce CHECK constraints. If you want this type of constraint actually enforced, you'll need to put that logic into BEFORE INSERT and BEFORE UPDATE triggers.

The CHECK clause is parsed but ignored by all storage engines

Reference: http://dev.mysql.com/doc/refman/5.6/en/create-table.html

Check constraint on multiple columns for BETWEEN

1 Answers1