Database Properties – Test Alternate Key (Index)

•Common Test Alternate Key Error at EDP Create Step

The Test Alternate Key (also referred to as test-level key or index), consists of the group of fields used to determine record uniqueness in the DT_TEST table. Prior to creating a new database, determine which fields are necessary to uniquely identify each test/analysis. Choose the minimum number of fields needed (typical recommendation). As part of the Test Alternate Key, the fields chosen will be required in all data sets.

The Test Alternate Key must include the following fields:

•FACILITY_ID: Identifier of the facility

•SAMPLE_ID: Identifier for the sample, unique within the facility

•ANALYTIC_METHOD: Name of the analytic method

The Test Alternate Key can optionally include the following fields:

•ANALYSIS_DATE: Date/time the sample was analyzed

•FRACTION: Such as Total, Dissolved, etc.

•COLUMN_NUMBER: Such as 1C, 2C, NA, etc.

•TEST_TYPE: Such as Initial, Reanalysis, Dilution, etc.

•IDENTIFIER: 60-character field that can be set to a value so that each row in the TAK is unique (any value client determines needed)

Warning: Consider the choice of TAK fields carefully, as this choice may be difficult or impossible to reverse once data is added to DT_TEST. Formats are not guaranteed to be compatible with all TAKs. For example, the Identifier field, which becomes required when added to the TAK, is not yet part of all standard EQuIS formats. INSERT errors may occur when loading data with such a format.

To view the EQuIS Database Properties, navigate to the EQuIS Professional Connect tab of the backstage (File) menu.

1.Click on a database to select it.

2.Right-click and select Properties from the context menu.

The Database Properties window opens and displays the Test-level Key Fields available to select for the database.

It is best to configure the permanent test level keys when creating a new EQuIS Database. While it is possible to change the test-level key after a database is created, it is not advisable. If data already exists in the database, it is only possible to add or remove fields if the change does not cause a conflict with the existing data. Otherwise, removing a field from the test-level key could create many duplicate entries that would require resolution.

Note: Alter the TAK through the database (i.e. SQL Server Management Studio), rather than the Database Update Form in EQuIS Professional. Consult a database administrator as needed.

Finding the Test Alternate Key for a Database

If you are not aware of the TAK for your database, the following SQL query can be run in either SQL Server

Management Studio (SSMS) or the EQuIS SQL Form in Professional:

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE CONSTRAINT_NAME = 'TestAlternateKey'

ORDER BY ORDINAL_POSITION

Common Test Alternate Key Error at EDP Create Step

Based on the database's Test Alternate Key settings, an error of this type may result in the Create step, if the EDD contains several records that are not unique. For this example, the reason this record is not passing the Create step is because the TEST_ID is the same as the CAS_RN of another record in the EDD. Adding another TAK could allow this record to be unique. Note that once a Test Alternate Key has been added to the database and new data has been loaded, it may no longer be possible to remove a Test Alternate Key. Backup the database prior to adding a Test Alternate Key, in case you want to revert back without the additional Test Alternate Key.

For more information, see Data Commit – TEST_ID and TEST_ID-1.