Table constraints

Informix®

Informix supports primary key, unique, foreign key, default and check constraints.

The constraint naming syntax is different in Informix and most other databases: Informix expects the "CONSTRAINT" keyword after the constraint specification:

CREATE TABLE emp (
  ...
  emp_code CHAR(10) UNIQUE CONSTRAINT pk_emp,
  ...
)

While other databases it before:

CREATE TABLE emp (
   ... 
   emp_code CHAR(10) CONSTRAINT pk_emp UNIQUE, 
   ...
)

Microsoft™ SQL Server

Microsoft SQL Server supports primary key, unique, foreign key, default and check constraints.

Constraint naming

The constraint naming clause must be placed before the constraint specification.

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.