sql - 如何使用 boolean 列(有点像单选按钮)强制执行 "uniqueness"？

Question

我有一个表描述多对一关系，比如说图片和相册之间的关系。我希望能够为每个相册选择一张且仅一张图片作为相册的封面图片。现在，表格是这样的:

AlbumID int primary key,
PictureID int,
IsCover bool

假设我不想更改整体架构(解决方案可能是将“is cover of”关系移至另一个表，定义一对一关系，但最好不要更改已经通过添加另一个表实现了查询)，有没有办法强制所有具有相同AlbumID但只有一个的条目将 IsCover 设置为 false？

我正在寻找服务器端解决方案。我正在使用 Microsoft SQL Server 2012。 UNIQUE 约束不能应用于此处，否则我只能为每张专辑提供封面和非封面。我已经在我的应用程序中实现了检查，但我真的希望有一个额外的安全层。

提前致谢。

Answer 1

可能唯一的过滤索引可以帮助您:

create table AlbumPics (
    AlbumID int not NULL,
    PictureID int not NULL,
    IsCover bit not NULL,
    primary key clustered (AlbumID, PictureID)
)

create unique index UX_AlbumPics_CoverPic
    on AlbumPics (AlbumID) where IsCover = 1

-- insert 3 pics for album 1, one of them is cover pic
insert into AlbumPics values
(1, 1, 0), (1, 2, 1), (1, 3, 0)

-- try to insert one more cover for album 1 - will fail
insert into AlbumPics values
(1, 4, 1)