Unique Index On Nullable Column?

I am reviewing some QAD tables and it appears that Progress lets you define a unique index that has a nullable column.

Is that correct?

This is in an OpenEdge 10.2 database.

(for QAD users, one example table is vph_hist having the nullable columns vph_ref and vph_pvo_id as part of the unique index vph_ref)
 

tamhas

ProgressTalk.com Sponsor
Null is not considered to break the uniqueness. I can understand why that may not be intuitive, but it has always been that way.
 
dBASE from its early days had what they called unique indexes which would let you find the one record matching a value in the index, and if there were more than one records with that value, the index would not let you find those others. Then various dBASE clone products copied that design and since dBASE was the first widely used DBMS, various other companies copied some aspects. So it wouldn't surprise me if Progress copied that design.

Does the unique index in Progress work like that? Or does it reject the changing of a value such that it would mean more than one record shares that value for the unique index key (aside from the null issue mentioned above)?
 

tamhas

ProgressTalk.com Sponsor
If an index component of a unique index is unknown value, then there can be any number of records that match. I don't believe that there is any way to FIND them individually without ROWIDs, but you can certainly FOR EACH through them. You can change the unknown value to a known value at any time, but of course, it must then be unique. The most common case I have seen this sort of thing is where is an index with couple of fields including something like ship date, that is not known at the time the record is created. One can, however, find the unique desired record using something like order number and then update that ship date. This is less confusing when ship date is in a non-unique index, but people have sometimes combined something like a unique ship id and the ship date into a single unique index where neither value is known at record creation.

Of course, this is just bad DB design, but there is a lot of that in the world.
 
You can change the unknown value to a known value at any time, but of course, it must then be unique.
So there can be multiple records with the same value (in the sense that the null must be recorded as some kind of byte/series of bytes in the index), but if you can't create a unique index on a table (say one one column) and insert two records with this same unique index key non-null value (like dBASE and some others will let you do).

If true, it is not as bad as I feared. Certainly not ideal, but not horrendous.
 

tamhas

ProgressTalk.com Sponsor
Are you familiar with the ABL unknown value? (There are actually several, but that doesn't matter here.) If the field in question is the only one in the index, e.g., something like shipping document, then the entire table can consist of records with the unknown value. If there are multiple fields in the index and only one can have unknown value, then there can be as many records as you want where all of the fields in the index match ... the one field with unknown value will keep them from violating uniqueness. Of course, when you fill in that field, you will have to supply unique values. You could, however, have duplicates in the field which started as unknown as long as the rest of the fields provided uniqueness.

Note that you can avoid this happening by marking the field as mandatory, but, of course, you need matching application logic to ensure that a value is assigned.
 
Oh fun. ;-)

"Note that you can avoid this happening by marking the field as mandatory, but, of course, you need matching application logic to ensure that a value is assigned."
When you mark a field as mandatory, does the sysprogress.syscolumns.nullflag get set to "N"? (If so this means I can't rely on that column to tell me if there are no nulls in this column, but I instead have to look at the data stored in the DB at the time to infer this).

Well, this application is large (it is QAD) and QAD has enough revenue that I hope they don't have such things, but then it is also somewhat customized, by a firm much much smaller than QAD, so I suppose there may be a case or two of this. We have a big data integrity checking task in all our systems (not just the QAD/Progress one), so I expect to find some oddities in the DB.
 

tamhas

ProgressTalk.com Sponsor
_Field._Mandatory will tell you whether the field is define as having no nulls, but I don't know that this is an absolute guarantee that there are no nulls in the data.

I expect to find some oddities in the DB.

I think you can count on absolutely, especially in an application as vintage as QAD.
 
Top