CHECK constraint with multiple conditions MySQL-CodePudding

CREATE TABLE shoesize( 
nr TINYINT NOT NULL,
shoesize VARCHAR(6) NOT NULL,

CHECK (shoesize = 'mini' OR 'medium' OR 'maxi'),
PRIMARY KEY (nr)
)engine=innodb;


insert into shoesize(nr,shoesize) values ('1','mini');
insert into shoesize(nr,shoesize) values ('2','medium');
insert into shoesize(nr,shoesize) values ('3','maxi');
insert into shoesize(nr,shoesize) values ('4','ultra');
insert into shoesize(nr,shoesize) values ('5','mega');

    Error Code: 3819. Check constraint 'shoesize_chk_1' is violated.    0.015 sec

I'm trying to make a constraint, that says only shoes where a certain text is accepted. However the constraint fires when I try to enter data. It should be possible to have an OR in the check as far as I'm concerned?

CodePudding user response：

Your problem is that only the first value mini is cheked, but you need to repeat the equation to get also the rest

CREATE TABLE shoesize( 
nr TINYINT NOT NULL,
shoesize VARCHAR(6) NOT NULL,

CHECK (shoesize = 'mini' OR  shoesize =  'medium' OR shoesize = 'maxi'),
PRIMARY KEY (nr)
)engine=innodb;


insert into shoesize(nr,shoesize) values ('1','mini');
insert into shoesize(nr,shoesize) values ('2','medium');
insert into shoesize(nr,shoesize) values ('3','maxi');
insert into shoesize(nr,shoesize) values ('4','ultra');
insert into shoesize(nr,shoesize) values ('5','mega');

SELECT * FROM shoesize

nr	shoesize
1	mini
2	medium
3	maxi

fiddle