DB is not enforcing index uniqueness

Are the values of either field set to unknown?

no. they are legit values.

hi,

it is an active index.

looked at the data again as per Rob's reply, and found there are indeed unknown (null) values. so, db apparently is not treating them as unique?

given the context, unknown is a perfectly legit value. it looks like there's some code revisions that needs to be done.

thank you all.

If you define the fields as mandatory and/or provide them with a non-null default value, you won't have the problem.  I know I was initially surprised by this behavior when I first discovered it ... maybe 30 years ago :) ... but there are circumstances where it can be desirable behavior.  An example being when a field is not known at the time that the record is created.  One may want the index to be unique once the value is known, but the value may only be determined later.

Thomas, it's amazing how we can twist our mind to explain everything regardless how odd it is... if you try hard enough any bug can be packed as a feature :)

even in openedge an unknown value equals an unknown value...

DEF VAR i1 AS INT NO-UNDO INIT ?.

DEF VAR i2 AS INT NO-UNDO INIT ?.

MESSAGE i1 = i2

 VIEW-AS ALERT-BOX INFO BUTTONS OK.

Marian, I don't see the problem.  Declare the field mandatory if you want true uniqueness.

> A null is a valid value, a null equal null (damn, even javascript say so)

Progress has 3-4 different types of the unknown values. They are not equal each other. ;-)

Marian, I disagree. Twice.

First of all, this behaviour is well documented and is there by design, so it is not a bug. It is in fact very useful. In some workflows a value will only become available later in a process, but when it does, it needs to be unique. Until then, it is absent and it cannot participate in the index.

Secondly, null is not a value, it is the absence of a value. The test null eq null does not "compare" two values, it checks if the value is absent.

Don't you just eventually compare two values in the end?

> On Apr 11, 2018, at 11:05 AM, jquerijero wrote:

>

> Don't you just eventually compare two values in the end?

>

>

>

yes.

Eventually Progress compares the sorting /weights/ of two values. Unknown value(s) has its own sorting weight.

That makes it even more confusing, are you implying two unknown values(s) have different sorting weight? When you sort two unknown values, which value comes first?

We can always agree to disagree Simon, even twice :)

Agreed, maybe there is a more appropriate word for this and believe it or not the same thing happens in Oracle but my feelings were the same there too... all this 'relative' uniques looks just suspicious to me.

Marian, while I tend to agree that the best possible design probably doesn't use this feature, I have seen lots of schemas that do.  A classic is something like the invoice number corresponding to a order.  Yes, better to use a join table and allow for many to many matches, but a lot of older schemas will put things like that right in the order.  The invoice number is unknown when the order is created, but it is still a valid order.  That index is also not used at that stage.  And, the order number is used to locate the record.  No one said we had to be talking about a primary index.

Note that in many use cases it is also benign.

Oh, and for some years I have **always** used a GUID regardless of any "natural" key that exists ... if for no other reason that if you create the record with the "wrong" natural key it can be awful to get it fixed.

Thomas, 

Thank you all for sharing your thoughts on this issue.

Spent hours looking to see what was wrong with the database, tables, indexes. This "feature" is something that's lurks in the background, and can get to bite you bad, really bad.

We can twist words that unknown, is just that, unknown. But null is a legit value, and that the unknown is equal to null. The database should have tweeted it cannot have duplicate values. :-)

The reason we claim this feature is not a bug is because at the time it was implemented back in the early eighties (long before there was any sort of SQL standard btw), it was done by a deliberate design choice, not by accident. I do agree that these days, it is not a desireable feature anymore and if we had it to do over again we would do something different.

Nobody seems to read documentation anymore, either.

gus bjorklund wrote:

> if we had it to do over again we would do something different.

What other way would be any better today?

Not having the unknown value at all?