Set field unique on a specific number-CodePudding

I'm developing an app and I need some help with SQL relating these two tables

Photos

PhotoType

Column
id
name

PhotoType is a table that contains info regarding if the photo is a profile picture, a banner, etc...

I don't want the database to allow more than one photoTypeID=1 per user

Is this possible with a UNIQUE Statement with a where clause? If so, how could that be achieved?

I'm using SQL Server 19

Thanks a lot!

CodePudding user response：

You can add a unique index with a filter:

CREATE UNIQUE INDEX OnePhotoTypeID1PerUser
  ON dbo.Photos(idUser)
  WHERE photoType_Id = 1;