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, 
   ...
)

SAP HANA®

SAP HANA supports primary key, unique, foreign key, default and check constraints.

Constraint naming

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

Primary keys

Like Informix, SAP HANA ® 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).

Note: SAP HANA primary key constraints do not allow NULLs; make sure your tables do not contain NULLs in the primary key columns.

Unique constraints

Like Informix, SAP HANA creates an index to enforce UNIQUE constraints (some RDBMS do not create indexes for constraints).

Note: SAP HANA unique constraints do allow NULLs; this can be forced with NOT NULL.

Foreign keys

Both Informix and SAP HANA support the ON DELETE CASCADE option.

Check constraints

Informix supports CHECK constraints, but this feature is not available in SAP HANA.

Null constraints

Informix and SAP HANA® support 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 SAP HANA ®.