Constraints

Constraint Naming Syntax

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

CREATE TABLE scott.emp (
  ...
  emp-code CHAR(10)
    UNIQUE [CONSTRAINT pk_emp],
  ...

CREATE TABLE scott.emp (
  ...
  emp-code CHAR(10)
    [CONSTRAINT pk_emp] UNIQUE,
  ... 

Primary Keys

Like Informix, Genero db creates an index to enforce PRIMARY KEY constraints (some RDBMS do not create indexes for constraints).

Unique Constraints

1. Like Informix, Genero db creates an index to enforce UNIQUE constraints (some RDBMS do not create indexes for constraints).
2. Using CREATE UNIQUE INDEX is silently converted to a unique constraint. To drop an index created as CREATE UNIQUE INDEX, you must do an ALTER TABLE DROP CONSTRAINT.
3. When using a unique constraint, Informix allows only one row with a NULL value, while Genero db allows several rows with NULL!

Foreign Keys

Both Informix and Genero db support the ON DELETE CASCADE option. To defer constraint checking, Informix provides the SET CONSTRAINT command while Genero db provides the DISABLE CONSTRAINTS hint.

Check constraints

He check condition may be any valid expression that can be evaluated to TRUE or FALSE, including functions and literals. You must verify that the expression is not Informix specific.

Null constraints

Informix and Genero db support NOT NULL constraints, but Informix does not allow you to give a name to "NOT NULL" constraints.

Solution

Constraint naming syntax

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 Genero db.