Oracle 5.0, Reference Manual

Page: 1065 / 3232

CREATE TABLE
Syntax
1045
operations. However, even if
foreign_key_checks = 0
[451] , MySQL does not permit the creation
of a foreign key constraint where a column references a nonmatching column type. Also, if a table has
foreign key constraints,
ALTER TABLE
cannot be used to alter the table to use another storage engine.
To change the storage engine, you must drop any foreign key constraints first.
You cannot issue
DROP TABLE
for a table that is referenced by a
FOREIGN KEY
constraint, unless you
do
SET foreign_key_checks = 0
. When you drop a table, any constraints that were defined in the
statement used to create that table are also dropped.
If you re-create a table that was dropped, it must have a definition that conforms to the foreign key
constraints referencing it. It must have the correct column names and types, and it must have indexes
on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and
refers to Error 150 in the error message, which means that a foreign key constraint was not correctly
formed. Similarly, if an
ALTER TABLE
fails due to Error 150, this means that a foreign key definition
would be incorrectly formed for the altered table.
For
InnoDB
tables, you can obtain a detailed explanation of the most recent
InnoDB
foreign key error
in the MySQL Server, by checking the output of
SHOW ENGINE INNODB STATUS
.
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 will
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. MySQL 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.
Additionally, MySQL requires that the referenced columns be indexed for
performance reasons. However, the system does not enforce a requirement that
the referenced columns be
UNIQUE
or be declared
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
UNIQUE
(including
PRIMARY
)
and
NOT NULL
keys.
Furthermore, MySQL does not recognize or support “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 storage engines that do not support foreign keys (such as
MyISAM
), MySQL
Server parses and ignores foreign key specifications.
13.1.10.3. Silent Column Specification Changes
In some cases, MySQL silently changes column specifications from those given in a
CREATE TABLE
or
ALTER TABLE
statement. These might be changes to a data type, to attributes associated with a data
type, or to an index specification.
All changes are subject to the internal row-size limit of 65,535 bytes, which may cause some attempts
at data type changes to fail. See
Section E.7.4, “Limits on Table Column Count and Row Size” .
Some silent column specification changes include modifications to attribute or index specifications: