Databases Reference
In-Depth Information
There are a wide range of additional features you can add to a
CREATE TABLE
statement.
Many of these are advanced and aren't discussed in this topic, but you can find more
information in the MySQL manual under the heading “CREATE TABLE syntax.”
These additional features include:
The
AUTO_INCREMENT
feature for numeric columns
This feature allows you to automatically create unique identifiers for a table. We
discuss it in detail later in this chapter in “The AUTO_INCREMENT Feature.”
Column comments
You can add a comment to a column; this is displayed when you use the
SHOW CREATE
TABLE
command that we discuss later in this section.
Foreign key constraints
You can tell MySQL to check whether data in one or more columns matches data
in another table. For example, you might want to prevent an album from being
added to the
music
database unless there's a matching artist in the
artist
table. As
we explain in “Table Types,” we don't recommend using foreign key constraints
for most applications. This feature is currently supported for only the InnoDB table
type.
Creating temporary tables
If you create a table using the keyword phrase
CREATE TEMPORARY TABLE
, it'll be
removed (
dropped
) when the monitor connection is closed. This is useful for copy-
ing and reformatting data because you don't have to remember to clean up.
Advanced table options
You can control a wide range of features of the table using table options. These
include the starting value of
AUTO_INCREMENT
, the way indexes and rows are stored,
and options to override the information that the MySQL query optimizer gathers
from the table.
Control over index structures
Since MySQL 4.1, for some table types, you've been able to control what type of
internal structure—such as a B-tree or hash table—MySQL uses for its indexes.
You can also tell MySQL that you want a full text or spatial data index on a column,
allowing special types of search.
You can check the
CREATE
TABLE
statement for a table using the
SHOW
CREATE
TABLE
state-
ment introduced in Chapter 5. This often shows you output that includes some of the
advanced features we've just discussed; the output rarely matches what you actually
typed to create the table. Here's an example for the
artist
table:
mysql>
SHOW CREATE TABLE artist;
+--------+------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------+
| artist | CREATE TABLE `artist` (
`artist_id` smallint(5) NOT NULL default '0',
`artist_name` char(128) default NULL,
