Hi,
your enum canbe NULL and is not indexed. So you can insert values not in enum,
replaced by NULL.
a solution is to UNIQUE index the enum column, and insert a unique bad value in
it.
Any value not in enum can not then be inseted :
mysql> create table enum_test(id int, name enum('test1','test2') NOT NULL,
UNIQUE KEY(name));
Query OK, 0 rows affected (0.06 sec)
mysql> desc enum_test;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | enum('test1','test2') | | PRI | test1 | |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO enum_test VALUES (0,'test3');
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> INSERT INTO enum_test(id) VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from enum_test;
+------+-------+
| id | name |
+------+-------+
| 0 | |
| 1 | test1 |
+------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO enum_test VALUES (1,'test3');
ERROR 1062 (23000): Duplicate entry '' for key 1
Mathias
Selon Michael Kruckenberg <[EMAIL PROTECTED]>:
> > Hi,
> > Use enum with a default type and let mysql do the check for you.
>
> The problem with an enum is that if you insert a value that's not in
> the enum, MySQL doesn't stop the insert, it leaves the column empty.
> This doesn't enforce data integrity like I think Chris wanted.
>
> mysql> desc enum_test;
> +-------+-----------------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+-----------------------+------+-----+---------+-------+
> | id | int(11) | YES | | NULL | |
> | name | enum('test1','test2') | YES | | test2 | |
> +-------+-----------------------+------+-----+---------+-------+
> 2 rows in set (0.25 sec)
>
> mysql> INSERT INTO enum_test VALUES (1,'test3');
> Query OK, 1 row affected, 1 warning (0.29 sec)
>
> mysql> SELECT * from enum_test;
> +------+------+
> | id | name |
> +------+------+
> | 1 | |
> +------+------+
> 1 row in set (0.00 sec)
>
> Mike Kruckenberg
> [EMAIL PROTECTED]
> "ProMySQL" Author
> http://www.amazon.com/exec/obidos/ASIN/159059505X
>
>
>
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
