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.

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.

In Dynamic Server, you cannot place a unique or primary-key constraint on a BLOB or CLOB column.

Opaque types of Dynamic Server 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.

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:

REFERENCES Clause

Use the REFERENCES clause to establish a referential relationship:

Within a table (that is, between two columns of the same table)

Between two tables (in other words, create a foreign key)

REFERENCES Clause:
 
|--REFERENCES--table--+------------------+---------------------->
                      |    .-,------.    |
                      |    V        |    |
                      '-(----column-+--)-'
 
>--+--------------------------+---------------------------------|
   |  (1)                     |
   '--------ON DELETE CASCADE-'

Notes:

Informix extension

Element	Description	Restrictions	Syntax
column	A referenced column	See Restrictions on Referential Constraints.	Identifier, p. Identifier
table	The referenced table	Must reside in the same database as the referencing table	Database Object Name, p. Database Object Name

The referencing column (the column being defined) is the column or set of columns that refers to the referenced column or set of columns. The referencing column can contain NULL and duplicate values, but values in the referenced column (or set of columns) must be unique.

The relationship between referenced and referencing columns is called a parent-child relationship, where the parent is the referenced column (primary key) and the child is the referencing column (foreign key). The referential constraint establishes this parent-child relationship.

When you create a referential constraint, the database server automatically creates an internal index on the constrained column or columns.