Scope
Languages: .NET, C/SQL, Visual Basic, Visual FoxPro, Java.
DBMSs: DB2 UDB for iSeries, DB2 Universal Database, Informix, Oracle, SQL Server
Description
Sometimes, there may be several sets of attributes that meet the conditions of a primary key. In these cases, we think of each set as a candidate key. Since the primary key is the one representing the table in other tables, it is necessary to decide which of the candidate keys will be the primary key.
The concept of non-primary candidate key is implemented in GeneXus by allowing user index definition with the 'Unique' attribute. This feature increases productivity by freeing GeneXus users from defining and calling uniqueness control procedures.
Conflict management at execution time
Given the existence of 'Unique' secondary indexes, there may be a conflict due to record duplication, not only in new record insertions, but also in updates. This will happen, for instance, when an attribute that belongs to a key candidate is updated.
When duplicate candidate key conflicts arise during insertion and update transactions, the "Record already exists" message will be displayed in the language corresponding to the Knowledge Base. When duplicate candidate key conflicts arise in the 'New'/ 'For Each' groups, the code belonging to the 'When duplicate' will be executed, if it actually exists, or else the insertion/update will not be executed.
Note that in those cases, by using this version the 'when duplicate' command is supported inside the 'For Each' group.
Reorganization conflict management
Given the existence of candidate keys, there are new cases of reorganization errors. In an IA, GeneXus detects possible conflict cases and warns IAR users about them. Besides, at execution time, the first reorganization step makes the necessary checks in order to prevent reorganizations from canceling in intermediate steps. If conflicts are detected, users have the possibility to stop the reorganization.
General considerations
Candidate keys made up of several attributes may have some attributes with a null value. From the uniqueness point of view, null values are equal to any other value. This is to say that only one record with a null value may exist in an attribute that is part of a candidate key. The browsing diagram of a For Each that has equal conditions ( '=' ) in all the attributes of a candidate key is shown as For First.
Examples
The following is an example of the use of candidate keys:
The data that we usually have from people, among other data, is an identifier and their identity card. For modeling this reality, we can define a transaction with the Identifier primary key (defined as auto-numbered) and one of the secondary attributes for representing the identity card. Then a user index will be defined on the latter, which will be Unique.
If you do not want to have several users (UserId) responding to the same e-mail address (UserMail) in a web site, one of these two should be the primary key, and the other the candidate key.