How to create CHECK constraint from Mysql Workbench
I want to add a check constraint like
ALTER TABLE fruit
ADD CONSTRAINT check_colour CHECK(color IN ('black','white', 'green'))
I can do this on the command line, however I cannot find a way to add a check constraint in the Mysql Workbench. I can find triggers but not check constraints.
+3
source to share
1 answer
MySQL accepts syntax for check constraint
, but does nothing. Hence, no checks.
The standard answer is to use a trigger to validate the values. In your case, I think you should have a table Colors
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 ensures that only valid colors are allowed. If you do this when the table is created you can add NOT NULL
.
+2
source to share