The IDERA SQL Data Compare Unique Keys tab is divided in two main sections. The top section contains two boxes with the list of unique keys that are potential candidates to be used as the comparison key for each table in the pair. Initially the boxes contain only the predefined unique keys that SQL Data Compare found, but you can add other unique keys that can be a combination of any and all the columns on the given table. The bottom section contains a box with the pair of unique keys that have been selected as the key to be used for the data comparison operation. SQL Data Compare picks the comparison key in the following order (whichever is found first):
- User-defined keys. User-defined keys (or custom keys) takes precedence over the built-in keys and indexes.
- Primary Key
- Unique Constraint
- Unique Index. Indexes that are not unique are not considered.
When the selected keys are not those that were picked by SQL Data Compare you should validate the keys by clicking on the Validate button. If the selected keys are not valid you will not be able to compare the those tables. By default the keys are only validated for type compatibility. If you wish to validate your custom defined keys for uniqueness then you should check that option in the Application Settings.
User-defined key validation is done as follows:
- Columns participating in the the user-defined key in both objects in the pair must have the same names
- Columns that participate in the key must have the same data type. Only the name of the type is checked. The length and other type properties are not considered.
- The uniqueness of the key is checked only if the Check custom key uniqueness option in the application settings under Miscellanies section is checked.
Selecting a key for the comparison. Double-clicking on a unique key on the top section will replace the selected key with the new one.
User-defined keys are not supported on Memory Tables.