Constraints

Constraint naming syntax

Both Informix^® and Microsoft™ SQL SERVER support primary key, unique, foreign key, default and check constraints. But the constraint naming syntax is different: SQL SERVER expects the "CONSTRAINT" keyword before the constraint specification and Informix expects it after.

Table 1. UNIQUE constraint example (Informix vs. Microsoft SQL Server)
Informix	Microsoft SQL SERVER
CREATE TABLE emp( ... emp_code CHAR(10) UNIQUE [CONSTRAINT pk_emp], ...	CREATE TABLE emp ( ... emp_code CHAR(10) [CONSTRAINT pk_emp] UNIQUE, ...

Important: SQL SERVER does not produce an error when using the Informix syntax of constraint naming.

The NULL / NOT NULL constraint

Note: Microsoft SQL SERVER creates columns as NOT NULL by default, when no NULL constraint is specified (colname datatype {NULL | NOT NULL}). A special option is provided to invert this behavior: ANSI_NULL_DFLT_ON. This option can be enabled with the SET command, or in the database options of SQL SERVER Management Studio.

Solutions

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 Microsoft SQL SERVER.

The NULL / NOT NULL constraint

Before using a database, you must check the "ANSI NULL Default" option in the database properties if you want to have the same default NULL constraint as in Informix databases.