adding constrain to a column

Question

Can anyone please help me with writing a query to add constraint on BestFigure column with "%/%" i.e. it should be 3/10 format.

Please refer the image.

Image Link

create table Player(
Player_No int Identity(1,1) Primary Key, Player_Name Varchar(20) Not null,
Category Varchar(20) check (Category='batsman' or Category='bowler' or Category='Allrounder'),
BestFigure Varchar(10) check (Bestfigure like'%/%'))

https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working#2115641 — jarlh, Nov 25 '18 at 18:52
The above link by @jarth is important, however, if you want the syntax (it won't prevent wrong data) let us know. — NoChance, Nov 25 '18 at 19:40

score 0 · Answer 1 · answered Nov 25 '18 at 19:41

I think a better solution is to store the two figures separately and then combine them:

BestFigure_left int,
BestFigure_right int,
BestFigure varchar(10) generated always as (concat(BestFigure_Left, '/', BestFigure_Right))

MySQL does not actually enforce check constraints (which is why a trigger is needed). If it did, you would do:

BestFigure Varchar(10) check (Bestfigure regexp '^[0-9]{1,3}/[0-9]{1,6}$')

Or something like that. I am unclear what the "3" and "10" mean in your description.

adding constrain to a column

1 Answers1