Progress
Embedded SQL-92
Guide and Reference
Candidate Keys
If you design your table to require that a column or combination of columns define a row as unique, you define the column or columns with a candidate key constraint.
EXAMPLEIn the next example, the employee number (empno) in the employee table uniquely identifies the row, and is the primary key for the employee table. There is another column that contains the social security number for the employee. Since the values for this column must be distinct for each row in the table, you should also define the ss_no column with a candidate key constraint:
You declare a column as a candidate key by using the keyword UNIQUE. Precede the UNIQUE keyword with the NOT NULL specification for that column. Like a primary key, a candidate key also uniquely identifies a row in a table. Note that a table can have only one primary key, but can have any number of candidate keys.
If you supply a duplicate value for a candidate key in an INSERT or UPDATE operation, the operation returns an error.
Column Level Candidate Key Constraint
You can use a column level candidate key constraint to ensure that a single column is unique in every row. For example, the ss_no column in the employee table must be unique. A column level candidate key constraint involves a single column.
EXAMPLEIn this example, the ss_no column is defined as a candidate key in the employee table by specifying the UNIQUE keyword in the column definition:
Table Level Candidate Key Constraint
If your application requires unique values for a combination of columns, define a table level candidate key constraint.
EXAMPLEFor example, in the order_item table the columns order_no and item_no together form a unique key. The combination of the order_no and item_no columns is a unique identifier in the order_item table, as shown in the following example:
In the following example, the first two insert statements succeed. Each of these statements inserts a row in the order_item table, and each specifies the value 322 for order_no. There is no column level candidate key constraint on the order_no column. The combination of values for the order_no and item_no columns is unique. The third insert statement fails with a table level candidate key constraint violation. The final insert statement fails with a violation of the not null constraint:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |