Using the PRIMARY KEY Constraint

A primary key is a column (or a set of columns, if you use the multiple-column constraint format) that contains a non-NULL, unique value for each row in a table. When you define a PRIMARY KEY constraint, the database server automatically creates an internal index on the column or columns that make up the primary key, and silently creates a NOT NULL constraint on the same column or columns.

You can designate only one primary key for a table. If you define a single column as the primary key, then it is unique by definition. You cannot explicitly give the same column a unique constraint.

You cannot place a unique or primary-key constraint on a BLOB or CLOB column.

Opaque types of Informix® support a primary key constraint only where a secondary-access method supports the uniqueness for that type. The default secondary-access method is a generic B-tree, which supports the equal( ) function. Therefore, if the definition of the opaque type includes the equal( ) function, a column of that opaque type can have a primary-key constraint.

You cannot place a primary-key constraint on a BYTE or TEXT column.

In the previous two examples, a unique constraint was placed on the column acc_num. The following example creates this column as the primary key for the accounts table:
   (acc_name  CHAR(12),
    acc_num   SERIAL PRIMARY KEY CONSTRAINT acc_num);

Copyright© 2019 HCL Technologies Limited