SQL adaptation guide For Oracle MySQL 5.x / Data dictionary |
Both Informix® and MySQL support primary key, unique, foreign key and default, but the constraint naming syntax is different: MySQL expects the "CONSTRAINT" keyword before the constraint specification and Informix expects it after.
Informix | MySQL |
---|---|
CREATE TABLE emp( ... emp_code CHAR(10) UNIQUE CONSTRAINT pk_emp, ... |
CREATE TABLE emp ( ... emp_code CHAR(10) CONSTRAINT pk_emp UNIQUE, ... |
Like Informix, MySQL 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).
In MySQL, the name of a PRIMARY KEY is PRIMARY.
Like Informix, MySQL creates an index to enforce UNIQUE constraints (some RDBMS do not create indexes for constraints).
When using a unique constraint, Informix allows only one row with a NULL value, while MySQL allows several rows with NULL! Using CREATE UNIQUE INDEX is obsolete.
Both Informix and MySQL support the ON DELETE CASCADE option. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same.
Check constraints are not yet supported in MySQL.
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 MySQL.