If you want to keep the enum column, you can set the strict SQL mode:
mysql> show variables like 'SQL_MODE';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> create table test2 (test enum('foo','bar'));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test2 values('baz');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'test' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> set session sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test2 values('bloop');
ERROR 1265 (01000): Data truncated for column 'test' at row 1
mysql> select * from test2;
+------+
| test |
+------+
| |
+------+
1 row in set (0.00 sec)
I'm running v5.0.51a; as you can see, the first invalid value was
truncated to '' with a warning, the second caused an error and did
not insert. I don't know what version you're running, perhaps this
does not apply to you. For more info:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/enum.html
- steve edberg
At 2:00 PM -0800 3/6/09, David Karr wrote:
Thanks. I thought that was the case, but I wanted to be sure.
On Fri, Mar 6, 2009 at 12:07 PM, Perrin Harkins <per...@elem.com> wrote:
I'm afraid enums are useless for anything except compressing your
data, since they don't reject bad values. If you want to limit a
field to a set of values, you need to use a lookup table and a foreign
key constraint.
- Perrin
On Fri, Mar 6, 2009 at 1:35 PM, David Karr <davidmichaelk...@gmail.com>
wrote:
> If I define an enum parameter for a stored program, and the calling code
> sends an invalid value, they get the less than useful "data truncated"
> error. Is it possible to define the stored program to produce better
error
> handling for that kind of error?
>
> This is probably a FAQ, but in general, it appears that error diagnostics
in
> stored programs are very primitive. Are there any plans in a roadmap to
> improve this?
>
--
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center sbedb...@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
