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

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 somet开发者_如何学运维hing 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 :(

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)

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

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