MySQL values limit to 1 and 0

Question

Hello guys I'm just a newbie so sorry for my question :)

I already tried using char, tinyint, bool and CHECK constraint.

Here's my CHECK constraint:

CHECK (user_type>=0 AND user_type<=1)

But still I can insert values more than 1, all I want is just 1s and 0s. I'll use it as type of my users. Although I can validate this in front-end level, I still want do it in a database itself.

Thanks in advance :)

Answer 1

The CHECK clause is parsed but ignored by all storage engines. (Source)

If you really want to do it you can create a table containing the values 0 and 1 and set up a foreign key constraint.

Answer 2

If you really want that constraint to be enforced make a table boolconstants with those 2 values and use a foreign key constraint. Bit of a hack but works for all enum values :)

Answer 3

use set or enum as the colum type. then define the values (eg: 1,0) :)

Answer 4

Since it's MySQL, wouldn't an enum column enforce the constraint as expected?

http://dev.mysql.com/doc/refman/5.0/en/enum.html

Answer 5

Thanks guys for your warm replies :) appreciate it much.

But I think i'll stick on the simpliest one

If you really want to do it you can create a table containing the values 0 and 1 and set up a foreign key constraint. from Sir Mark.

Thanks again :)