-2

I have a unique constraint on one of my tables:

CREATE UNIQUE INDEX `role_contextid_targetid_ownerid_type_endedat_unique` on `role` (`contextId`, `targetId`, `ownerId`, `type`, `endedAt`)

You can see it is part of the db definition here: enter image description here

But for some reason it is still allowing multiple entries which share all of their contextId, targetId, ownerId, type, endedAt values. Notice the last three items below:

enter image description here

Am I misunderstanding what a multi-column unique index is supposed to do? In not, why did SQLite allow me to add items that break these rules?

Seph Reed
  • 8,797
  • 11
  • 60
  • 125
  • 2
    These screenshots are awful. With 7K reputation, you should now better than do this. – PChemGuy Oct 26 '22 at 06:33
  • @PChemGuy, the table part or the part where I gave a copy with the relevant code in text form? How would you do tables? Or would you prefer I gave that big blob of raw sqlite without the highlight? – Seph Reed Oct 26 '22 at 15:35
  • https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) Have you heard of markdown? – PChemGuy Oct 26 '22 at 16:01

1 Answers1

1

Here's what docs are saying

For the purposes of unique indices, all NULL values are considered different from all other NULL values and are thus unique.

So, it considers NULLS as different values which makes all the problem lines unique from the DB's point of view

dbfiddle

ekochergin
  • 4,109
  • 2
  • 12
  • 19