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.

EXAMPLE

In 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:

EXEC SQL 
     CREATE TABLE employee ( 
          empno  INTEGER NOT NULL PRIMARY KEY, 
          ss_no  INTEGER NOT NULL UNIQUE, 
          ename  CHAR (19), 
          sal  NUMERIC (10, 2), 
          deptno  INTEGER NOT NULL 
     ) ; 

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.

EXAMPLE

In 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:

EXEC SQL 
     CREATE TABLE employee 
     ( 
          empno  INTEGER NOT NULL PRIMARY KEY, 
          ss_no  INTEGER NOT NULL UNIQUE, 
          ename  CHAR (19), 
          sal  NUMERIC (10, 2), 
          deptno  INTEGER NOT NULL 
     ) ; 

Table Level Candidate Key Constraint

If your application requires unique values for a combination of columns, define a table level candidate key constraint.

EXAMPLE

For 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:

EXEC SQL 
     CREATE TABLE order_item ( 
          order_no INTEGER NOT NULL, 
          item_no  INTEGER NOT NULL, 
          qty  INTEGER 
          UNIQUE (order_no, item_no) 
     ) ; 

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:

/* 
** 1. This statement succeeds, inserting one row. 
*/ 
EXEC SQL 
     INSERT INTO order_item  
          VALUES ( 322, 55, 288 ) ; 
/* 
** 2. This statement supplies the same value, 322, for order_no. 
**    The insert succeeds, since the combination of the values for 
**    order_no and item_no is unique. 
*/ 
EXEC SQL 
     INSERT INTO order_item  
          VALUES ( 322, 56, 288 ) ; 
/* 
** 3. This statement fails with a table level candidate key constraint 
**    violation. The database does not allow two rows with the same 
**    order_no AND item_no. 
*/ 
EXEC SQL 
     INSERT INTO order_item  
          VALUES ( 322, 55, 144 ) ; 
/* 
** 4. This statement violates the NOT NULL constraint. 
**    The operation returns this error: 
**    "Null value supplied for a mandatory (not null) column."  
*/ 
EXEC SQL 
     INSERT INTO order_item (item_no, qty) 
          VALUES ( 56, 288) ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095