Tech Tip: Utility Method to Apply Unique Property Removal With A Changed Datafile

PRODUCT: 4D | VERSION: 14.x | PLATFORM: Mac & Win

Published On: August 11, 2016

Some table properties are not fully stored on the Structure and may be stored in multiple locations. One of these properties is the Unique property which is stored on both the Datafile and the Structure file. This can cause some issues. One scenario is the case of when an indexed field was originally unique, then the unique property was disabled. When changing to a different datafile, the unique property for the field is still considered set.

This causes the database to generate a duplicate values error when it appears that it should not.

To fix this the unique property must be retoggled on and off for each datafile or the index for the field must be recreated. Below is a utility method that will recreate the index for the tables that are affected by this problem:

// Util_UniqueDisabledFix
// ----------------------------------------------------

ARRAY LONGINT($table_al;0)
ARRAY LONGINT($field_al;0)
ARRAY TEXT($ndxName_at;0)
ARRAY LONGINT($ndxType_al;0)
ARRAY POINTER($field_ap;1)

C_LONGINT($i)
C_POINTER($field_ptr)

Begin SQL
   SELECT _USER_IND_COLUMNS.TABLE_ID,_USER_IND_COLUMNS.COLUMN_ID,
   _USER_IND_COLUMNS.INDEX_NAME,_USER_INDEXES.INDEX_TYPE
   FROM _USER_IND_COLUMNS
   INNER JOIN _USER_INDEXES
   ON _USER_IND_COLUMNS.INDEX_ID=_USER_INDEXES.INDEX_ID
   INNER JOIN _USER_COLUMNS
   ON _USER_IND_COLUMNS.TABLE_ID=_USER_COLUMNS.TABLE_ID
   AND _USER_IND_COLUMNS.COLUMN_ID=_USER_COLUMNS.COLUMN_ID
   WHERE _USER_INDEXES.UNIQUENESS=True
   AND _USER_COLUMNS.UNIQUENESS=False
   INTO :$table_al, :$field_al, :$ndxName_at, :$ndxType_al;
End SQL

For ($i;1;Size of array($table_al))
   $field_ptr:=Field($table_al{$i};$field_al{$i})
   $field_ap{1}:=$field_ptr
   DELETE INDEX($field_ptr)
   CREATE INDEX(Table($table_al{$i})->;$field_ap;$ndxType_al{$i};$ndxName_at{$i})
End for

This is able to find the issue by looking at the _USER_COLUMNS system table which has the structure's storage of the unique property while the _USER_INDEXES system table has the datafile's setting of the unique property.

When the error occurs the following two lines are the conditions that causes an error:

_USER_INDEXES.UNIQUENESS=True
AND _USER_COLUMNS.UNIQUENESS=False

Which should be:

_USER_INDEXES.UNIQUENESS=False
AND _USER_COLUMNS.UNIQUENESS=False

The method recreates the indexes with the same names and types as before for each of the afflicted indexes.

System Tables Documentation:

http://doc.4d.com/4Dv15/4D/15/System-Tables.300-2288116.en.html