p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 (http://p2p.wrox.com/forumdisplay.php?f=430)
-   -   CHECK constraint (http://p2p.wrox.com/showthread.php?t=87429)

masterlayouts May 2nd, 2012 01:28 PM

CHECK constraint
 
I have a problem with CONSTRAINTs
This is what I am using:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 1.1.8 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.16 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86 |
| version_compile_os | Win32 |
+-------------------------+------------------------------+

I tried to add a CHECK constraint for the gender. I tried both these and without success. I saw a lot of people report it as a bug, but not sure if it's a but or I do something wrong.

Code:

CHECK(gender IN ('M', 'F'));
CHECK (gender LIKE '[MF]');

I tried

Quote:

ALTER TABLE users
ADD CONSTRAINT CHECK (gender LIKE '[MF]')
as well as

Code:

CONSTRAINT ck_gender CHECK (gender in ( 'M', 'F' ) )
when define the table.

Is my syntax wrong? Thank you for your time.

Rod Stephens May 2nd, 2012 04:10 PM

Sorry but both of those look valid to me. But I've seen some posts that say MySQL doesn't support CHECK constraints. See this thread:

http://forums.mysql.com/read.php?136...474#msg-152474

Be sure to read the replies. They say you may be able to use a trigger to perform the job of the CHECK constraint.

In this case you could also build a lookup table and use it as a foreign key to validate the field. That seems like serious overkill but it should work.


All times are GMT -4. The time now is 09:41 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.