Constraints

Constraint naming syntax

Both Informix® and PostgreSQL support primary key, unique, foreign key, default and check constraints, but the constraint naming syntax is different. PostgreSQL expects the "CONSTRAINT" keyword before the constraint specification and Informix expects it after.

UNIQUE constraint example

Table 1. UNIQUE constraint example (Informix vs. PostgreSQL)
Informix PostgreSQL
CREATE TABLE emp (
  ...
  emp_code CHAR(10)
   UNIQUE CONSTRAINT pk_emp,
  ...
CREATE TABLE emp (
   ...
   emp_code CHAR(10)
     CONSTRAINT pk_emp UNIQUE,
   ...

Unique constraints

Note: When using a unique constraint, Informix allows only one row with a NULL value, while PostgreSQL allows several rows with NULL!

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 PostgreSQL.