CODEWITHSUNDEEP
mysqlsql
person0
person0

Reputation: 1388

Can mysql be used to constrain each row of a VARCHAR to have length equal to INT column in same row?

I have two columns in a mysql table that are set by user preference:

custom_string VARCHAR(255) NOT NULL

items_per_row TINYINT UNSIGNED NOT NULL DEFAULT '5'

EXAMPLE:

+--------------------------------------+
|             preferences              |
+--------------------------------------+
| id | custom_string | items_per_group |
+--------------------------------------+
|  1 |   TRINKETS    |        8        |
+--------------------------------------+
|  2 |               |        5        |
+--------------------------------------+
|  3 |    MYSTUFF    |        7        |
+--------------------------------------+

items_per_row is a required field. The custom_string field is optionally used to personalize the way grouped items in the list are displayed.

The user is able to update these preferences any time. Here is a crude example of how items might be displayed:

enter image description here

I am wanting to find a way to constrain the length of each user's custom_string so that if it is not blank it must have a length that is exactly the same as the corresponding items_per_group value. I can easily validate the user input with both javascript and PHP and prevent the data from being entered into the database if it doesn't comply with this requirement, however, is there a way to set this constraint within mysql so that an attempt to have an 'invalid' string would be rejected?

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

MySQL does not implement check constraints. With them, this would be easy:

alter table preferences add constraint chk_preferences_custom
    check (custom_string is null or length(custom_string) = items_per_group);

Your only option in MySQL is to use a trigger for this purpose.

In practice, it might be simpler to check at the application level when you insert/update custom_string.

Upvotes: 2

Related Questions