Check constraint with If-Else-Statement in MySQL?

Question

Is it possible to define a check constraint within a table like this?

CREATE TABLE game
(
duration SMALLINT UNSIGNED NOT NULL CHECK ( duration = duration > 65535 ? duration/1000 : duration)
);

For context: I want to safe game data including the game duration. FOr some reason its sometimes stored in seconds and sometimes in milliseconds. Is this possible or do I have to do this If-Statement within the sourcecode outside of SQL like

CREATE TABLE game
(
duration SMALLINT UNSIGNED NOT NULL
);

int durationFromJSON = json.duration;
short duration = = durationFromJSON > 65535 ? durationFromJSON /1000 : durationFromJSON;
preparedStatement = connection.prepareStatement("INSERT INTO game(duration) VALUES ?");
preparedStatement.setShort(1, duration);
preparedStatement.executeUpdate();

Note: A game usually lasts between 10 and 60 minutes. In this case the duration checked of 65535 (limit of SMALLINT UNSIGNED) should be fine because a game will never last for only 65.535 seconds and never longer than 65,535 seconds.

You cannot even try to insert a value greater than 65535 into SMALLINT UNSIGNED column. This insertion will fail always with "out of range" error. — Akina
– Akina, Commented Mar 28, 2022 at 9:22
@danblack is this if function available within the check constraint? — Lara
– Lara, Commented Mar 28, 2022 at 9:25
From a software development standpoint, the problem here is "for some reason its sometimes stored in seconds and sometimes in milliseconds.". You're trying to fix that problem in the wrong place (database), when you should be making sure that it's always stored in the same format. — Kayaman
– Kayaman, Commented Mar 28, 2022 at 9:33
Well I kinda dont have any influence on the data stored since I dont have access to the system behind this JSON-api — Lara
– Lara, Commented Mar 28, 2022 at 9:38

score 1 · Accepted Answer · 2022-03-28 09:35:37Z

1

When you insert values into a table you can't process the value. There are 2 options: a trigger to modify the values after insert or a virtual column using an if or case statement.
Here is an example of the latter, which seems simpler: less code to add and all in the same table definition.

create table game(
id int,
dur int, 
duration int
GENERATED ALWAYS AS (case when dur > 65000 then dur /1000 else dur end) STORED);

insert into game (id, dur) values
(1,1000),
(2,100000);

select * from game;

id |    dur | duration
-: | -----: | -------:
 1 |   1000 |     1000
 2 | 100000 |      100

db<>fiddle here

edited Mar 28, 2022 at 9:35

answered Mar 28, 2022 at 9:25

user18098820

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

Collectives™ on Stack Overflow

Check constraint with If-Else-Statement in MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related