0

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

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
confusedWarrior
  • 539
  • 3
  • 14

2 Answers2

0

I could do something like below,

create unique index tests_unique_name_schoolId_notDeleted
    on tests (name, schoolId) where isDeleted = false
confusedWarrior
  • 539
  • 3
  • 14
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,710
  • 2
  • 10
  • 16