使用通配符值时的 SQL 唯一性约束

Question

请考虑下表，其中指定了一周中特定日期禁止吃的水果。 DayOfWeek 可为空，其中 NULL 表示一周中的所有天禁止食用此类水果。

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

是否可以在此表上实现一个约束，阻止我插入值 (Kiwi, Monday)，因为现有 ( 已在星期一（以及每隔一天）禁止新西兰人使用Kiwi, NULL) 行。

优选地，这应该在不使用触发器的情况下实现。

Answer 1

除非你有充分的理由，否则你不应该改变 NULL 的含义。 Null 反映了一个值是未知的，所以我将其读为“我们不知道一周中的哪一天新西兰人被禁止”。然后，您可以更改逻辑来存储 Kiwi 被禁止的一周中的每一天的记录。

如果您需要编写一个查询，要求给我周一所有的禁果，该怎么办？您需要将查询编写为

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

更有效且更易于理解的查询将消除 or 子句。

Answer 2

我同意其他人的观点，我可能会瞄准与您展示的模型不同的模型，但如果您对它感兴趣，那么以下似乎可以满足您的要求：

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.

*我最初尝试在不引入 < 的情况下执行此操作code>Weekdays 表，并将其内联显示在视图定义中，作为文字行的子选择。但是您无法创建对此类视图强制执行我们想要的约束的索引。

Answer 3

我个人不喜欢 NULL 意味着全部的想法，但将其更改为在 NULL 可能超出范围时每天包含一行。

如果触发器不是一个选项，我会查看检查约束并设置一个函数来测试您试图避免的条件。

Answer 4

尝试此链接 http://www.java2s.com/Code/SQL/ Select-Clause/SettingaUniqueConstraint.htm

对于 MySQL，您可以设置唯一的约束。