Table constraints

Informix®

Informix supports primary key, unique, foreign key, default and check constraints.

The constraint naming syntax is different in Informix and most other databases: Informix expects the constraint name after the constraint definition:

CREATE TABLE emp (
  ...
  emp_code CHAR(10) UNIQUE CONSTRAINT pk_emp,
  ...
)

While other SQL database brands require to specify the constraint name before the constraint definition:

CREATE TABLE emp (
   ... 
   emp_code CHAR(10) CONSTRAINT pk_emp UNIQUE, 
   ...
)

Netezza®

Netezza supports primary key, unique, foreign key, default and check constraints in a limited manner.

Important: Netezza allows you to create tables with the UNIQUE and PRIMARY KEY and FOREIGN KEY syntax, but the constraints are not enforced.

Constraint naming syntax

The constraint naming clause must be placed before the constraint specification.

Solution

The database interface does not convert constraint naming expressions when creating tables from BDL programs. Review the database creation scripts to adapt the constraint-naming clauses for Netezza.

Since Netezza does not enforce constraints, you must test for unique values and foreign key references at the program level.