SQL uniqueness constraint when using wildcard values

Consider the following table, which specifies fruit that is forbidden on given days of the week. DayOfWeek is nullable, where a NULL signifies that this type of fruit is forbidden on all days of the week.

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

Is it possible to implement a constraint on this table that prevents me from inserting the values (Kiwi, Monday), since Kiwis are already banned on Mondays (and every other day) by the existing (Kiwi, NULL) row.

Preferably this should be impl开发者_JAVA技巧emented without the use of triggers.

Unless you have a really good justification you shouldn't change the meaning of NULL. Null reflects a value is unknown so I would read this as We don't know what day of the week kiwi's are banned. You would then change your logic to store a record for each day of the week that Kiwi is banned on.

What if you need to write a query which says give me all forbidden fruit for monday. You need to write your query as

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

A more efficent and easier to understand query would eliminate the or clause.

I agree with others that I probably would aim for a different model than the one you've shown, but if you're sold on it, then the following seems to do what you want:

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)
)

Not sure what the PK is for the Exclusions table should be, not obvious from what you've shown, but it's intended to be your table. We need to introduce the Weekdays table to make the later view work*. Now populate it:

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'

And your sample data:

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'

Now we create the view to implement your constraint:

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)

And, if we try to insert the row you don't want us to:

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

We get:

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.

*I initially tried to do this without introducing the Weekdays table, and have it appear inline in the view definition, as a subselect of literal rows. But you can't create the index that enforces the constraint we want on such a view.

I am personally not fond of the idea of NULL mean all, but changing this to include a row for each day when NULL is probably out of scope.

If a trigger is not an option, I would look at a CHECK CONSTRAINT and setting up a function that tests the condition you are trying to avoid.

Try this link http://www.java2s.com/Code/SQL/Select-Clause/SettingaUniqueConstraint.htm

For MySQL you can set unique contraints.