How to enforce "uniqueness" with boolean column (kinda like radio buttons)?

Question

I have a table that describes a multi-to-one relationship, let's say between Pictures and Albums. I want to be able to select one and only one picture per album as the album's cover picture. Right now, the table is like this:

AlbumID int primary key,
PictureID int,
IsCover bool

Supposing that I don't want to change the overall schema (a solution could be to move the "is cover of" relationship to another table, defining a one-to-one relationship, but it is preferred not to change the already implemented queries by adding another table), is there a way to enforce that all of the entries with the same AlbumID BUT ONE have IsCover set to false?

I'm looking for server-side solutions. I'm working with Microsoft SQL Server 2012. The UNIQUE constraint can't be applied here, otherwise I could only have a cover and a non-cover for every album. I have already implemented a check in my application, but I would really like to have an additional layer of safety.

Thanks in advance.

Answer 1

Probably unique filtered index is the thing that may help you:

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)