Referential constraints

You can identify the primary and foreign keys in each table to place referential constraints on columns. Build a relational data model contains information about how you identify these keys.

When you are trying to pick columns for primary and foreign keys, almost all data type combinations must match. For example, if you define a primary key as a CHAR data type, you must also define the foreign key as a CHAR data type.

However, when you specify a SERIAL data type on a primary key in one table, you specify an INTEGER on the foreign key of the relationship. Similarly, when you specify a SERIAL8 data type on a primary key in one table, you specify an INT8 on the foreign key of the relationship; and when you specify a BIGSERIAL data type on a primary key in one table, you specify a BIGINT data type on the foreign key of the relationship.

The only data type combinations that you can mix in a relationship are as follows:
  • SERIAL and INTEGER
  • SERIAL8 and INT8
  • BIGSERIAL and BIGINT

For information about how to create a table with referential constraints, see the CREATE TABLE and ALTER TABLE statements in the HCL OneDB™ Guide to SQL: Syntax.