RE: Second Request - Limit Filed Input
Shankar Unni writes: I don't see implementation of "CHECK" CONSTRAINTS in the TODO list (e.g. CONSTRAINT val_ck check (val = 0 and val =5) Is this planned? I do see FOREIGN KEY constraints on the 4.0 list, so there is a way to do this kind of checking once this is implemented: * Create an associated "range-check" table with a primary key column, and add rows for the values you want to allow in your table column. * Put a foreign key constraint on your column to point at that table. This will at least ensure that you get a constraint violation if you attempt to insert something illegal. This works only for small ranges or other enumeration types (integer or string). E.g. create table check_phase_number (phase integer); insert into check_phase_number values (1),(2),(3),(4),(5); create table check_state_abbrev (state char(2)); insert into check_state_abbrev values ('AK'),('AL'),('CA'), ... ('VT'),('WA'); create table my_state_migration ( phase integer constraint phase_fk foreign key references check_phase_number(phase), state char(2) constraint state_fk foreign key references check_state_abbrev(state), ... ); Hi! We shall definitely consider putting it in 4.* branch. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Second Request - Limit Filed Input
I don't see implementation of "CHECK" CONSTRAINTS in the TODO list (e.g. CONSTRAINT val_ck check (val = 0 and val =5) Is this planned? I do see FOREIGN KEY constraints on the 4.0 list, so there is a way to do this kind of checking once this is implemented: * Create an associated "range-check" table with a primary key column, and add rows for the values you want to allow in your table column. * Put a foreign key constraint on your column to point at that table. This will at least ensure that you get a constraint violation if you attempt to insert something illegal. This works only for small ranges or other enumeration types (integer or string). E.g. create table check_phase_number (phase integer); insert into check_phase_number values (1),(2),(3),(4),(5); create table check_state_abbrev (state char(2)); insert into check_state_abbrev values ('AK'),('AL'),('CA'), ... ('VT'),('WA'); create table my_state_migration ( phase integer constraint phase_fk foreign key references check_phase_number(phase), state char(2) constraint state_fk foreign key references check_state_abbrev(state), ... ); -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 8:27 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Second Request - Limit Filed Input you can use either an ENUM or a SET. Other than that, no. Cal http://www.calevans.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 10:05 AM To: [EMAIL PROTECTED] Subject: Second Request - Limit Filed Input Is there a way in MySQL to limit the values that a (numeric) field can hold. For example if I want a given field to only have the values 1 - 10 can I do this and can it be done in the definition of the field when the table is created? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Second Request - Limit Filed Input
Hi. You can use an ENUM column type assuming the numerical range is less than 65535 numbers. http://www.mysql.com/doc/E/N/ENUM.html -ravi. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 11:05 AM To: [EMAIL PROTECTED] Subject: Second Request - Limit Filed Input Is there a way in MySQL to limit the values that a (numeric) field can hold. For example if I want a given field to only have the values 1 - 10 can I do this and can it be done in the definition of the field when the table is created? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Second Request - Limit Filed Input
you can use either an ENUM or a SET. Other than that, no. Cal http://www.calevans.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 10:05 AM To: [EMAIL PROTECTED] Subject: Second Request - Limit Filed Input Is there a way in MySQL to limit the values that a (numeric) field can hold. For example if I want a given field to only have the values 1 - 10 can I do this and can it be done in the definition of the field when the table is created? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Second Request - Limit Filed Input
At 11:22 AM 3/20/2001 -0500, Ravi Raman wrote: You can use an ENUM column type assuming the numerical range is less than 65535 numbers. http://www.mysql.com/doc/E/N/ENUM.html One problem I've seen with enums is that I end up with a blank ('') field if I try to insert a row with a value in the enum column that doesn't fit. Is there a way to get the insert to fail on "bad input" instead? For example: mysql CREATE TABLE Stats ( - Status enum('good','bad') NOT NULL - ); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO Stats VALUES ('neither'); Query OK, 1 row affected (0.00 sec) (Can I get this insert to FAIL instead? because...) mysql select * FROM Stats; ++ | Status | ++ || ++ 1 row in set (0.01 sec) (this isn't always a good thing). -bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Second Request - Limit Filed Input
Hi. It's kind of counter-intuitive to me as well, but the rationale is that there obviously was an error if you have a "" value for an ENUM column. this theoretically allows you at some later time to use that indicator to do some sort of error-check or update/delete all erroneous rows, or whatever you decide to do. ...in other words, it's a feature, not a bug. -ravi. -Original Message- From: Bill Marrs [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 12:48 PM To: [EMAIL PROTECTED] Subject: RE: Second Request - Limit Filed Input At 11:22 AM 3/20/2001 -0500, Ravi Raman wrote: You can use an ENUM column type assuming the numerical range is less than 65535 numbers. http://www.mysql.com/doc/E/N/ENUM.html One problem I've seen with enums is that I end up with a blank ('') field if I try to insert a row with a value in the enum column that doesn't fit. Is there a way to get the insert to fail on "bad input" instead? For example: mysql CREATE TABLE Stats ( - Status enum('good','bad') NOT NULL - ); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO Stats VALUES ('neither'); Query OK, 1 row affected (0.00 sec) (Can I get this insert to FAIL instead? because...) mysql select * FROM Stats; ++ | Status | ++ || ++ 1 row in set (0.01 sec) (this isn't always a good thing). -bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Second Request - Limit Filed Input
Yes, but it makes it hard to track down who/what piece of code entered those bad values. You just have to clean them up later and wonder. Not the end of the world, but I have some cases where I'd rather fail and deal with the error at time of input (have them re-enter or whatever). thanks -bill At 01:52 PM 3/20/2001 -0500, Ravi Raman wrote: It's kind of counter-intuitive to me as well, but the rationale is that there obviously was an error if you have a "" value for an ENUM column. this theoretically allows you at some later time to use that indicator to do some sort of error-check or update/delete all erroneous rows, or whatever you decide to do. ...in other words, it's a feature, not a bug. -ravi. -Original Message- From: Bill Marrs [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 12:48 PM To: [EMAIL PROTECTED] Subject: RE: Second Request - Limit Filed Input At 11:22 AM 3/20/2001 -0500, Ravi Raman wrote: You can use an ENUM column type assuming the numerical range is less than 65535 numbers. http://www.mysql.com/doc/E/N/ENUM.html One problem I've seen with enums is that I end up with a blank ('') field if I try to insert a row with a value in the enum column that doesn't fit. Is there a way to get the insert to fail on "bad input" instead? For example: mysql CREATE TABLE Stats ( - Status enum('good','bad') NOT NULL - ); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO Stats VALUES ('neither'); Query OK, 1 row affected (0.00 sec) (Can I get this insert to FAIL instead? because...) mysql select * FROM Stats; ++ | Status | ++ || ++ 1 row in set (0.01 sec) (this isn't always a good thing). -bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php