Ensuring Key Values Are Unique
Unique keys can be enforced automatically for hash, ISAM, and B-tree tables using the modify procedure.
Benefits of Unique Keys
Benefits of unique keys are:
• A good database design that provides unique keys enhances performance.
• You are automatically ensured that all data added to the table has unique keys.
• The Ingres optimizer recognizes tables that have unique keys and uses this information to plan queries wisely.
In most cases unique keys are an advantage in your data organization.
Disadvantages of Unique Keys
The disadvantages of unique keys include a small performance impact in maintaining uniqueness. You must also plan your table use so that you do not add two rows with the same key value.
Specify Unique Keys
In VDBA, unique keys can be specified as Row or Statement in the Unique group box in the Structure of Table and Structure of Index dialogs:
• Row indicates that uniqueness is checked as each row is inserted.
• Statement indicates that uniqueness is checked after the update statement is executed.
If you do not want to create a unique key, select the No option.
Example: Prevent the Addition of Two Names with the Same Number
The following example prevents the addition of two employees in the emp table with the same empno:
modify emp to isam unique on empno;
In VDBA
1. Open the Structure of Table dialog for the emp table.
2. Select Isam from the Structure drop-down list.
3. Enable Row in the Unique radio button group box.
4. Enable the empno column in the Columns list.
If a new employee is added with the same employee number as an existing record in the table, the row is not added, and you are returned a row count of zero.
Note: An error is not returned in this case; only the row count shows that the row was not added. Be aware of this if you are writing application programs using unique keys.
Example: Modify a Table to Hash and Prevent the Addition of Two Names with the Same Number
The following example modifies the emp table to hash and prevents the addition of two employees in the emp table with the same empno.
modify emp to hash unique on empno;
In VDBA:
1. Open the Structure of Table dialog for the emp table.
2. Select Hash from the Structure drop-down list.
3. Enable Row in the Unique radio button group box.
4. Enable the empno column in the Columns list.
The rows in the following example have unique keys. Although employee #17 and #18 have the same records except for their employee numbers, the employee numbers are unique, so these are valid rows after the modification:
Empno Name Age Salary
| 17 | Shigio | 29| 28000.000|
| 18 | Shigio | 29| 28000.000|
| 1 | Aitken | 35| 50000.000|
The following two rows do not have unique keys. These two rows cannot both exist in the emp table after modification to hash unique on empno:
Empno Name Age Salary
| 17 | Shigio | 29| 28000.000|
| 17 | Aitken | 35| 50000.000|