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.

Data type combinations that you can mix in a relationship are as follows (1st column is the foreign key and 2nd column is the primary key):
  • INTEGER and SERIAL
  • INTEGER and BIGINT
  • INTEGER and BIGSERIAL
  • INTEGER and INT8
  • INTEGER and SERIAL8
  • SERIAL and INTEGER
  • SERIAL and BIGINT
  • SERIAL and BIGSERIAL
  • SERIAL and INT8
  • SERIAL and SERIAL8
  • INT8 and BIGINT
  • INT8 and SERIAL8
  • INT8 and BIGSERIAL
  • SERIAL8 and INT8
  • SERIAL8 and BIGINT
  • SERIAL8 and BIGSERIAL
  • BIGINT and INT8
  • BIGINT and BIGSERIAL
  • BIGINT and SERIAL8
  • BIGSERIAL and BIGINT
  • BIGSERIAL and INT8
  • BIGSERIAL and SERIAL8

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