0

I have a table TBLPRODUCTS.

The table contains a field listed tinyint(1) not null default 0

The field can take values between 0-9. Is there a way in mysql to allow only two values in this field: either 1 or 0

Bubba Yakoza
  • 749
  • 2
  • 9
  • 17
  • 1
    One possible solution provided here: http://stackoverflow.com/a/9249893/ – Aziz Shaikh Nov 27 '14 at 05:47
  • @AzizShaikh: I'd upvote that as an answer. Seems better than a trigger to emulate the non-functional CHECK constraint. – Thilo Nov 27 '14 at 05:49
  • Actually, a `TINYINT(1)` can take 255 possible values, ranging from -128 and 127. You could use triggers (BEFORE INSERT and BEFORE UPDATE), or a foreign key constraint. With the foreign key constraint, you can raise an exception when a value is not in range. With triggers, you have more options as to how to handle values. – spencer7593 Nov 27 '14 at 05:57
  • Where does the input come from? That's where I'd put the constraint. – Strawberry Nov 27 '14 at 08:18

1 Answers1

0

you can convert existing column to BIT(1), example sql

ALTER IGNORE TABLE Persons MODIFY P_Id BIT(1) DEFAULT 0

Note: all existing values >=1 will be converted to 1;

Anil
  • 3,722
  • 2
  • 24
  • 49