3

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 :)

Barry
  • 1,587
  • 2
  • 13
  • 19
  • Related: http://stackoverflow.com/questions/3414421/does-my-sql-supports-check-constraint – Mark Byers Aug 14 '10 at 17:59
  • strager - `INT` columns can contain values other than 0 and 1. The `(1)` only sets the display width; it doesn't constrain the values that may be stored. Even if it did constrain the values stored, though, you'd still be able to store 2 to 9. – Hammerite Aug 14 '10 at 18:40
  • 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 :) – Barry Aug 14 '10 at 18:21

4 Answers4

4

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

Thomas Clayson
  • 29,657
  • 26
  • 147
  • 224
3

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
0

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 :)

extraneon
  • 23,575
  • 2
  • 47
  • 51
0

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

Jarrett Meyer
  • 19,333
  • 6
  • 58
  • 52