CHECK constraint with multiple conditions MySQL

Question

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?

I strongly recommend using a separate table for sizes with a valid key constraint. What you've done there is essentially hard code the valid values for shoesize into the CHECK. Create a table for all known shoe sizes, give each one a primary id, and use a foreign key constraint to refer to the size from the shoesize table . — erik258
– erik258, Commented Sep 17, 2022 at 13:37

nbk · Accepted Answer · 2022-09-17 13:34:51Z

2

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

answered Sep 17, 2022 at 13:34

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

erik258 Over a year ago

@xtremesnus take a good look at how nbk use the fiddle site to demonstrate the solution. You can do the same next time to demonstrate the problem, and to experiment for yourself.

Collectives™ on Stack Overflow

CHECK constraint with multiple conditions MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related