sql - SQL Server Only Allow True for One of Each Type

Question

I have a SQL Server table where I'd like only one of each type to be allowed to be true. For instance, if I had the following structure:

|  ID  | Sport      | IsTheBest   |
|:-----|------------|------------:|
|  1   | Basketball |           1 |
|  2   | Basketball |           0 |
|  3   | Basketball |           0 |
|  4   | Basketball |           0 |
|  5   | Baseball   |           0 |
|  6   | Baseball   |           1 |
|  7   | Baseball   |           0 |

I want a constraint in place to ensure that no other record is allowed where Basketball has IsTheBest set to true. Said another way, if I tried to edit ID 2 to be IsTheBest = true, I want it to fail.

标签： sqlsql-servertsql

Answer 1

You can use a filtered unique index:

create unique index unq_sport_isthebest
    on t(sport)
    where isthebest = 1;