Progress
Embedded SQL-92
Guide and Reference


Primary Keys

Your application might require that a database table contain one or more columns to identify a row uniquely. For example, in the supplier table the supp_no column value must be unique. Every row of the table is uniquely identified by this column value. A single column or a group of columns can be the principal unique identifier of the table. The principal unique identifier is called a primary key. A table can contain only one primary key constraint.

If you supply a duplicate value for a primary key column in an INSERT operation, the operation returns an error.

Column Level Primary Key Constraint

You can design your database table so that there is only one column that distinguishes a given row from other rows. In this case, a single column is the unique identifier of the table. For example, the supp_no column is a column level primary key for the supplier table. Column level primary key constraints are defined in the column definitions of a table.

EXAMPLE

In this example, the supp_no column is a unique identifier in the supplier table, and the key consists of only one column. This example shows how to create a column level primary key on the supplier table:

EXEC SQL 
     CREATE TABLE supplier 
          ( 
          supp_no  INTEGER NOT NULL PRIMARY KEY, 
          last_name  CHAR (30), 
          status  SMALLINT, 
          city  CHAR (20) 
          ) ; 

Table Level Primary Key Constraint

You can design your database table to require more than one column to identify a row as unique. The content of your data might require this. You can use a combination of columns to ensure that each row is unique.

EXAMPLE

For example, the columns supp_no and item_no uniquely identify a row in the supplier_item table. You must specify a combination of columns that form a primary key at the table level. The following example shows a table level primary key specification:

EXEC SQL 
     CREATE TABLE supplier_item 
          ( 
          supp_no  INTEGER NOT NULL, 
          item_no  INTEGER NOT NULL, 
          qty  INTEGER NOT NULL DEFAULT 0 
          CONSTRAINT prim_constr 
          PRIMARY KEY (supp_no, item_no) 
          ) ; 

Since multiple columns (supp_no, item_no) are in the primary key, you must specify the constraint at the table level. In this example, the constraint named prim_constr is the primary key of the supplier_item table.


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