CODEWITHSUNDEEP
t-sqlconstraints
user797257
user797257

Reputation:

Specify constraint that prevents either of the two columns to be null when other is not null?

Suppose I want to store an interval in a table, where the interval can be specified as either a single date, or as a range, so, the fields would be:

| id | since | until | at_date |

Now, I'd like situations like since is null and until is not null or since is not null and until is null never to happen, but both since is null and until is null and since is not null and until is not null are fine (the first one would be only fine, if at_date is specified though).

Perhaps it would make sense to "merge" until and since into one column, but in my situation it makes sense some times to only select one or another, so I think it's ok to have them separately.

EDIT:

create table menu_availability
(menu_id int not null,
 daily_serving_start time null,
 daily_serving_end time null,
 weekly_service_off tinyint null,
 one_time_service_off date null,
 constraint ch_valid_week_day
    check ((weekly_service_off is null) 
        or (weekly_service_off <= 6 and weekly_service_off >= 0)),
 constraint ch_non_empty_schedule
    check ((daily_serving_start is not null and daily_serving_end is not null)
        or (daily_serving_start is null and daily_serving_end is null)),
 constraint ch_either_week_or_time_set
    check (daily_serving_start is not null 
        or weekly_service_off is not null 
        or one_time_service_off is not null),
 constraint ch_only_week_or_one_time
    check ((weekly_service_off is null and one_time_service_off is not null)
        or (weekly_service_off is not null and one_time_service_off is null)))

This is what I have so far... but it's really messy...

Upvotes: 0

Views: 663

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

I think you should remove at_date and only use since and until for all your intervals.

Make until non inclusive so a value that in at_date would be '2013-03-01' is stored like

since      until
2013-03-01 2013-03-02

Update:

You can create a persisted computed bit column using a case statement that does not allow nulls.

create table YourTable
(
  id int identity primary key,
  since datetime,
  until datetime,
  at_date datetime,
  sn as case when since is null and until is null or
                  since is not null and until is not null then cast(1 as bit)
        end persisted not null
)

Or with a check constraint

create table T
(
  id int identity primary key,
  since datetime,
  until datetime,
  at_date datetime,
  constraint ch_sn check(since is null and until is null or
                         since is not null and until is not null)
)

Upvotes: 2

Related Questions