Is there any way to force at least one column NOT NULL 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;
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".