CODEWITHSUNDEEP
mysqlvalidationdatabase-designtypes
Phill Pafford
Phill Pafford

Reputation: 85348

MySQL VARCHAR like Data type with MIN and MAX character lengths

Looking for a data type that is similar to VARCHAR as to the values I would like, but is there a way to have a MIN/MAX character length?

VARCHAR(6,10)

This would have a minimum of 6 characters and a maximum of 10 characters.

Upvotes: 1

Views: 4909

Answers (2)

cEz
cEz

Reputation: 5062

You could add a trigger to throw an exception when the values are outside of the range, e.g.

DELIMITER $$

CREATE TRIGGER `insert_table_var` BEFORE INSERT ON `table` 
FOR EACH ROW
BEGIN
     DECLARE str_len INT DEFAULT 0;
     DECLARE max_len INT DEFAULT 10;
     DECLARE min_len INT DEFAULT 6;

     SET str_len = LENGTH(NEW.col);

     IF str_len > max_len OR str_len < min_len 
     THEN
           CALL col_length_outside_range_error();
     END IF;
END $$
DELIMITER ;;

Whilst SIGNAL is not available, calling an undefined stored procedure would suffice (in this case col_length_outside_range_error). Otherwise, I think that the application using the database is going to need to do the checks.

Upvotes: 2

newtover
newtover

Reputation: 32094

This is called CHECK constraint in SQL and it is ignored by MySQL.

The CHECK clause is parsed but ignored by all storage engines.

Upvotes: 2

Related Questions