![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961054.webp)
CREATE TABLE
Syntax
1034
Prefixes can be up to 1000 bytes long (767 bytes for
InnoDB
tables). Note that prefix limits are
measured in bytes, whereas the prefix length in
CREATE TABLE
statements is interpreted as
number of characters for nonbinary data types (
CHAR
,
VARCHAR
,
TEXT
). Take this into account when
specifying a prefix length for a column that uses a multi-byte character set.
• An
index_col_name
specification can end with
ASC
or
DESC
. These keywords are permitted for
future extensions for specifying ascending or descending index value storage. Currently, they are
parsed but ignored; index values are always stored in ascending order.
• When you use
ORDER BY
or
GROUP BY
on a column in a
SELECT
, the server sorts values using only
the initial number of bytes indicated by the
max_sort_length
[471]
system variable.
• You can create special
FULLTEXT
indexes, which are used for full-text searches. Only the
MyISAM
storage engine supports
FULLTEXT
indexes. They can be created only from
CHAR
,
VARCHAR
,
and
TEXT
columns. Indexing always happens over the entire column; column prefix indexing is
not supported and any prefix length is ignored if specified. See
Section 12.9, “Full-Text Search
Functions”
, for details of operation.
• You can create
SPATIAL
indexes on spatial data types. Spatial types are supported only for
MyISAM
tables and indexed columns must be declared as
NOT NULL
. See
Section 12.16, “Spatial
Extensions”
.
•
InnoDB
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. For
more detailed information and examples, see
Section 13.1.10.2, “Using
FOREIGN KEY
Constraints”
.
For information specific to foreign keys in
InnoDB
, see
Section 14.2.3.4, “
InnoDB
and
FOREIGN
KEY
Constraints”
.
For other storage engines, MySQL Server parses and ignores the
FOREIGN KEY
and
REFERENCES
syntax in
CREATE TABLE
statements. The
CHECK
clause is parsed but ignored by all storage
engines. See
Section 1.8.5.4, “Foreign Keys”
.
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.
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.
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 does not recognize or support “inline
REFERENCES
specifications”
(as defined in the SQL standard) where the references are defined as part of
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...