Build 1857 FK Schema Change Bug Fix

[Harlan Haynie]

Build 1.857 for R:Base 6.5++ corrects a long standing bug which allowed database corruption to occur when dropping a column from a table.  Specifically, if TableA contains columns A1, A2, and A3 (in my specific database, a text, a date, and an integer data type), and all three columns participate in a UNIQUE KEY, and that UNIQUE KEY is referenced by TableB also having columns A1, A2, and A3, and another column, A4, is dropped from tableA, it can corrupt R:Base.  This corruption is manifested in several ways:  often, but now always, list table tableB shows the “consrnt” attribute FOREIGN KEY, but, the rest of the constraint listing is missing, i.e., it should say FOREIGN KEY REFERENCES columnname, but, the “REFERENCES columnname” portion of the syntax is missing; output tableb.str, unload structure for tableB yields a file with missing syntax in the alter table area where the structure to rebuild the FOREIGN KEY reference should have been listed; and  the sys_indexes.sys_foreign_key column contains “0” instead of the value of the index that it should be referencing.    When R:Base makes a schema change to a table, such as dropping a column, the internal R:Base integer names for the indexes are re-numbered.  In the case of a compound foreign key reference against a UNIQUE, this re-numbering routine was unable to correctly provide the new index numbers for the foreign key references.  The result was the appearance of zeros in sys_indexes.sys_foreign_key where valid index numbers should have been.   Applications can react to this corruption in a variety of ways such as failing to insert a row into a table when the insert is called by en EEP.  This corruption is not cured by reload.  If you unload structure and trace the input, you can usually find the deficient alter table commands as they error out.  If you wanted, you could probably write a little routine to search sys_indexes for sys_foreign_key values that are “0” when the expected value should be a non-zero  integer, because the column participates in a KEY.  My recollection is that this is a long standing bug; I think it was present in 4.5++ and maybe even earlier; the problem has always been being able to reproduce it.  Late last week, John Corrigan was able to provide Wayne with a script that reproduced this bug with 100% repeatability.  Once it was reproduce-able, Wayne eradicated it faster than Mr. Orkin himself.     Harlan