Table-Level Primary Key Constraint

In some database tables, it might not be possible to uniquely identify a row with just one column. But there might exist a combination of columns that when taken together, can uniquely identify a row. For example, the columns supp_no, item_no uniquely identify a row of the table supplier_item. Hence, a combination of columns that form a primary key should be specified at the table level.

The following example shows the table level primary key specification:

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 more than one column (supp_no, item_no) are contained in the primary key, the constraint is defined at the table level. In the above example, the constraint prim_constr is the primary key of table supplier_item.