I have a table structured as below,
tests --------------------------- schoolId | name | isDeleted I don't want to allow duplicate test name for an school if it is active i.e isDeleted = false
I am not sure how can I put unique constraint for fields taking only one value for boolean fields isDeleted=false
I could do something like below,
create unique index tests_unique_name_schoolId_notDeleted on tests (name, schoolId) where isDeleted = false You should change the query of the index like the below query:
create unique index tests_unique_name_schoolId_notDeleted on tests (name) where isDeleted = false; The name and isDeleted columns should be mixed up together and put where isDeleted = false means only a row is unique when isDeleted is false when the name column is the same. On the other hand, you can store too many records with isDeleted = true and these rows not be unique and not stored in the index.
