Oracle version: 12.2.0.1
I have obviously thoroughly misunderstood the nature of a unique index. I understood per the docs (https://docs.oracle.com/html/E25494_01/indexes003.htm#i1106547) that "Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns)."
However, I am working with an index created as follows:
create unique index d11.plan_id_src_u
on d11.plan_details (id,parent_plan_id)
tablespace d11_indx_23
nologging
I thought this meant that there had to be a 1:1 relationship between (plan) id and parent_plan_id but many plans can and normally do have the same parent ID as the below output shows:
select parent_plan_id, count(id) from plan_details group by parent_plan_id
parent_plan_id count(id)
388542 (null)
1107319 3
1107320 3
1107321 3
1107322 3
1107327 3
I have updated some plans to add them to the same parent plan this morning to rule out a 'no validate' type scenario but I was successfully able to add them. Everything I am reading is supporting my misunderstanding so if someone could either explain to me why it is possible to have duplicates in a unique index or point me at some documentation I'd really appreciate it. I don't get how this index exists at all.