-> artist_id SMALLINT(5) NOT NULL DEFAULT 0

There area wide range of additional features you can add to a CREATE TABLE
statement.
Many of these are advanced and aren’t discussed in this book, 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 fora 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 copying 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 fora 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) default NULL,

Download 4.24 Mb.

Share with your friends: