4

I want to add a check constraint for example

ALTER TABLE fruit
ADD CONSTRAINT check_colour CHECK(color IN ('black','white', 'green'))

I can do it in the command line, however, I can't find the option to add a check constraint in Mysql Workbench. I can only find triggers, but not check constraints.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
Joey
  • 51
  • 1
  • 2
  • 5

1 Answers1

2

MySQL allows the syntax for check constraint, but doesn't do anything about it. Hence, no checks.

The standard answer is to use a trigger to check the values. In your case, though, I think you should have a Colors table and a foreign key constraint:

create table Colours (
    ColourId int not null auto_increment primary key,
    Colour varchar(255)
);

ALTER TABLE fruits
    ADD COLUMN ColourId FOREIGN KEY (ColourID) REFERENCES Colours(ColourId);

This will ensure that only valid colors are allowed. If you do this when the table is created, you can add NOT NULL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786