Mysql adding a constraint: force a field to get specific values

Question

I have a simple mysql table with the following attributes: Name, Surname, Role.

I want the Role field to get only 2 possible values: Supervisor or Operator and to result in error when a query tries to insert something different from that 2 values.

For example, i want the following query to return an error:

INSERT INTO tablename (name,surname,role) VALUES ('max','power','footballplayer');

I tried setting the field Role as a ENUM or SET type but it will just leave the field empty instead of firing and error :(

score 2 · Accepted Answer · answered Mar 01 '11 at 15:50

You need to change sql_mode to avoid insert.

mysql> create table check_values (
    -> id int not null auto_increment primary key,
    -> name varchar(50),
    -> role enum ('max','power','fp')
    -> )engine = myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into check_values (name,role) values ('nick','max');
Query OK, 1 row affected (0.00 sec)

mysql> insert into check_values (name,role) values ('john','other');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'role' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from check_values;
+----+------+------+
| id | name | role |
+----+------+------+
|  1 | nick | max  |
|  2 | john |      |
+----+------+------+
2 rows in set (0.00 sec)

mysql> set sql_mode = 'traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into check_values (name,role) values ('frank','other');
ERROR 1265 (01000): Data truncated for column 'role' at row 1
mysql> select * from check_values;
+----+------+------+
| id | name | role |
+----+------+------+
|  1 | nick | max  |
|  2 | john |      |
+----+------+------+
2 rows in set (0.00 sec)

So, i could use the sql_mode switch before every query that need a check... thats exactly what i need for my project. Thank you. — fat, Mar 03 '11 at 08:22
hi @nick, the last error what type of SQLException would throw for a jdbc driver? — user3290180, Jan 05 '16 at 11:33

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

Do you see any error when you use enum as shown here ? http://dev.mysql.com/doc/refman/5.0/en/enum.html

One other approach would be to create a look-up type of table with the allowed roles and create a foreign key constraint from this table to the role table. That would be more appropriate if yu use the check constraint at multiple places or if you have a larger list of values to check against.

MYSQL, I believe, does not support the check constraint directly, if that's what you are looking for. Check these links.

CHECK constraint in MySQL is not working

MySQL and Check Constraints

Using Foreign Keys to replace check constraint

edited May 23 '17 at 12:31

Community

1
1

answered Mar 01 '11 at 15:19

Rajesh Chamarthi

18,568
4
40
67

Thank you i should have looked for answers in this site before asking because that link shows the same problem as mine. Your solution using a foreign key to a "role table" would work but it looks a bit overkill to me in this situation. Thank u again. – fat Mar 01 '11 at 15:26

score 0 · Answer 3 · answered Mar 01 '11 at 15:28

0

use trigger.. to throw the error manually
http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

answered Mar 01 '11 at 15:28

Ujjwal Manandhar

2,194
16
20

Mysql adding a constraint: force a field to get specific values

3 Answers3