Блог

Рассмотрим следующую таблицу, в которой указаны фрукты, запрещенные в определенные дни недели. DayOfWeek имеет значение null, где NULL означает, что этот тип фруктов запрещен во все дни недели.

 Fruit        DayOfWeek
----------------------
Kiwi         NULL
Apples       Monday
Strawberries Monday
Oranges      Tuesday
Bananas      Wednesday
Pineapple    Thursday
  

Возможно ли реализовать ограничение для этой таблицы, которое не позволяет мне вставлять значения (Kiwi, Monday) , поскольку киви уже запрещены по понедельникам (и через день) существующей (Kiwi, NULL) строкой.

Предпочтительно это должно быть реализовано без использования триггеров.

Если у вас нет действительно хорошего обоснования, вы не должны изменять значение NULL. Значение Null отражает неизвестное значение, поэтому я бы прочитал это, поскольку мы не знаем, в какой день недели киви запрещены. Затем вы изменили бы свою логику, чтобы сохранить запись для каждого дня недели, в который заблокирован Kiwi.

Что, если вам нужно написать запрос, в котором говорится, дайте мне все запретные плоды к понедельнику. Вам нужно написать свой запрос как

select * from BadFruit where DayOfWeek='Monday' || DayOfWeek is null

Более эффективный и простой для понимания запрос исключил бы предложение or.

Я согласен с другими, что я, вероятно, стремился бы к другой модели, отличной от той, которую вы показали, но если вам это нравится, то следующее, похоже, делает то, что вы хотите:

 create table dbo.Weekdays (
    DayOfWeek varchar(10) not null,
    constraint PK_Weekdays PRIMARY KEY (DayOfWeek)
)
go
create table dbo.Exclusions (
    Fruit varchar(20) not null,
    DayOfWeek varchar(10) null,
    /* PK? */
    constraint FK_Exclusions FOREIGN KEY (DayOfWeek) references Weekdays (DayOfWeek)
)
  

Не уверен, каким должен быть PK для таблицы исключений, не очевидно из того, что вы показали, но это должна быть ваша таблица. Нам нужно ввести Weekdays таблицу, чтобы заставить работать более поздний просмотр *. Теперь заполните его:

 insert into dbo.Weekdays (DayOfWeek)
select 'Monday' union all
select 'Tuesday' union all
select 'Wednesday' union all
select 'Thursday' union all
select 'Friday' union all
select 'Saturday' union all
select 'Sunday'
  

И ваши примеры данных:

 insert into dbo.Exclusions (Fruit,DayOfWeek)
select 'Kiwi',NULL union all
select 'Apples','Monday' union all
select 'Strawberries','Monday' union all
select 'Oranges','Tuesday' union all
select 'Bananas','Wednesday' union all
select 'Pineapple','Thursday'
  

Теперь мы создаем представление для реализации вашего ограничения:

 create view dbo.Exclusions_NullExpanded
with schemabinding
as
    select
        e.Fruit,
        wd.DayOfWeek
    from
        dbo.Exclusions e
            inner join
        dbo.Weekdays wd
            on
                e.DayOfWeek = wd.DayOfWeek or
                e.DayOfWeek is null
go
create unique clustered index IX_Exclusions_NoDups on dbo.Exclusions_NullExpanded (Fruit,DayOfWeek)
  

И, если мы попытаемся вставить строку, которую вы не хотите, чтобы мы:

 insert into dbo.Exclusions (Fruit,DayOfWeek)
select 'Kiwi','Monday'
  

Мы получаем:

 Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Exclusions_NullExpanded' with unique index 'IX_Exclusions_NoDups'.
The statement has been terminated.
  

* Изначально я пытался сделать это без представления Weekdays таблицы и отобразить ее встроенной в определение представления как подвыборку буквенных строк. Но вы не можете создать индекс, который применяет ограничение, которое мы хотим для такого представления.

Лично мне не нравится идея NULL означать все, но изменяю это, чтобы включить строку для каждого дня, когда NULL, вероятно, выходит за рамки.

Если триггер не подходит, я бы рассмотрел ОГРАНИЧЕНИЕ ПРОВЕРКИ и настройку функции, которая проверяет условие, которого вы пытаетесь избежать.

Попробуйте эту ссылку http://www.java2s.com/Code/SQL/Select-Clause/SettingaUniqueConstraint.htm

Для MySQL вы можете установить уникальные ограничения.