content_image/836427.jpg
InnoDB  and NDB  tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. Both ON DELETE  and ON UPDATE  actions on foreign keys are supported. For more detailed information and examples, see Section 13.1.20.5, “FOREIGN KEY Constraints” . 

For other storage engines, MySQL Server parses and ignores the FOREIGN KEY  and REFERENCES syntax in CREATE TABLE  statements. See Section 1.7.2.3, “FOREIGN KEY Constraint Differences” . 

<img src='content_image/836427.jpg'>

## Important 

For users familiar with the ANSI/ISO SQL Standard, please note that no storage engine, including InnoDB , recognizes or enforces the MATCH  clause used in referential integrity constraint definitions. Use of an explicit MATCH  clause does not have the specified effect, and also causes ON DELETE  and ON UPDATE clauses to be ignored. For these reasons, specifying MATCH  should be avoided. 

The MATCH  clause in the SQL standard controls how NULL  values in a composite (multiple-column) foreign key are handled when comparing to a primary key. InnoDB  essentially implements the semantics defined by MATCH SIMPLE , which permit a foreign key to be all or partially NULL . In that case, the (child table) row containing such a foreign key is permitted to be inserted, and does not match any row in the referenced (parent) table. It is possible to implement other semantics using triggers. 

• reference_option 

Additionally, MySQL requires that the referenced columns be indexed for performance. However, InnoDB  does not enforce any requirement that the referenced columns be declared UNIQUE  or NOT NULL . The handling of foreign key references to nonunique keys or keys that contain NULL  values is not well defined for operations such as UPDATE  or DELETE CASCADE . You are advised to use foreign keys that reference only keys that are both UNIQUE  (or PRIMARY ) and NOT NULL . 

MySQL parses but ignores “inline REFERENCES  specifications”  (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES  clauses only when specified as part of a separate FOREIGN KEY  specification. 

For information about the RESTRICT , CASCADE , SET NULL , NO ACTION , and SET DEFAULT  options, see Section 13.1.20.5, “FOREIGN KEY Constraints” . 

## Table Options 

Table options are used to optimize the behavior of the table. In most cases, you do not have to specify any of them. These options apply to all storage engines unless otherwise indicated. Options that do not apply to a given storage engine may be accepted and remembered as part of the table definition. Such options then apply if you later use ALTER TABLE  to convert the table to use a different storage engine. 

• ENGINE 

Specifies the storage engine for the table, using one of the names shown in the following table. The engine name can be unquoted or quoted. The quoted name ’DEFAULT’  is recognized but ignored. 

