0

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
1

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".

4
  • Wow, thanks a lot. Do I set it as CONSTRAINT? – pixelngrain Dec 23 '19 at 6:44
  • @pixelngrain Of course. See examples by the link provided. – Akina Dec 23 '19 at 6:45
  • getting syntax error ` SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ date IS NULL = 1)` I have update question with the code. – pixelngrain Dec 23 '19 at 6:49
  • thanks for your help. I have fixed it. You can see the code in answer. – pixelngrain Dec 23 '19 at 7:09
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, privacy policy and cookie policy

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