Constraints

Constraint naming syntax

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

UNIQUE constraint example

Table 1. UNIQUE constraint example (Informix vs Netezza)
Informix Netezza
CREATE TABLE emp (
  ...
  emp_code CHAR(10) UNIQUE
  CONSTRAINT pk_emp, 
CREATE TABLE emp (
   ... 
   emp_code CHAR(10) 
   CONSTRAINT pk_emp UNIQUE, 
   ... 
Important: Netezza allows you to create tables with the UNIQUE and PRIMARY KEY and FOREIGN KEY syntax, but the constraints are not enforced.

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.