SQL adaptation guide For IBM DB2 UDB 8.x, 9x / Data dictionary |
Both Informix® and BD2 support primary key, unique, foreign key, default and check constraints. But the constraint naming syntax is different: DB2® expects the "CONSTRAINT" keyword before the constraint specification, and Informix expects it after .
UNIQUE constraint example:
Informix | IBM DB2 |
---|---|
CREATE TABLE emp ( ... emp_code CHAR(10) UNIQUE CONSTRAINT pk_emp, |
CREATE TABLE emp ( ... emp_code CHAR(10) CONSTRAINT pk_emp UNIQUE, ... |
Like Informix, DB2 creates an index to enforce PRIMARY KEY constraints (some RDBMS do not create indexes for constraints). Using "CREATE UNIQUE INDEX" to define unique constraints is obsolete (use primary keys or a secondary key instead).
Like Informix, DB2 creates an index to enforce UNIQUE constraints (some RDBMS do not create indexes for constraints).
Both Informix and DB2 support the ON DELETE CASCADE option.
The 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.
Informix and DB2 support NOT NULL constraints, but Informix does not allow you to give a name to "NOT NULL" constraints.
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 DB2.