1

I have a schedule_days table that is having id, day, date columns.

id - auto increment, primary key
day - weekday - enum(weekdays) default not null
date - a specific date - default not null

The schedule is for the train time table. That will have the same routine for all weeks except for any specific date, such as any festival. Thus I have set both null as I want to set either day or date.

If the date is specified, then I can get the day from it, so it doesn't make sense to ask or force the user to set day, which may cause human error.

I may use TRIGGER but would like to know if there is any better way to do in MySQL - 8.0.* version

Table Query

CREATE TABLE IF NOT EXISTS schedule_days
(
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    day  ENUM ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY') DEFAULT NULL,
    date DATE DEFAULT NULL,
    INDEX staff_index (day),
    CONSTRAINT day_day_check CHECK (`day` IS NULL + `date` IS NULL = 1) # check constraint
) ENGINE = INNODB;
0

2 Answers 2

2

If you use version 8.0.16 or above, use CHECK constraint.

Something like

CHECK COALESCE(`day`, `date`) IS NOT NULL 

for "at least one is not null", or

CHECK `day` IS NULL + `date` IS NULL = 1

for "strongly one is not null".

0
0

With the help of @Akina I am able to resolve the issue. Hee is the final query.

I have created CONSTRAINT using CHECK (as @Akina suggested) for day and date either table IS NOT NULL

CONSTRAINT day_date_check CHECK ((day IS NOT NULL) OR (date IS NOT NULL))

Table Query

CREATE TABLE IF NOT EXISTS schedule_days
(
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    day  ENUM ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY') DEFAULT NULL,
    date DATE DEFAULT NULL,
    INDEX schedule_day (day),
    CONSTRAINT day_date_check CHECK ((day IS NOT NULL) OR (date IS NOT NULL))
) ENGINE = INNODB;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.