1

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

confusedWarrior
  • 938
  • 2
  • 14
  • 30

2 Answers2

1

I could do something like below,

create unique index tests_unique_name_schoolId_notDeleted
    on tests (name, schoolId) where isDeleted = false
confusedWarrior
  • 938
  • 2
  • 14
  • 30
0

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.

Demo

Pooya
  • 2,968
  • 2
  • 12
  • 18