Databases Reference
In-Depth Information
components are separated by commas. There are other elements that you can add to a
CREATE TABLE
statement, and we'll discuss some in a moment.
Let's discuss the column specifications. The basic syntax is as follows:
name type
[NOT
NULL | NULL] [DEFAULT
value
]
. The
name
field is the column name, and it has the same
limitations as database names, as discussed in the previous section. It can be at most
64 characters in length, backward and forward slashes aren't allowed, periods aren't
allowed, it can't end in whitespace, and case sensitivity is dependent on the underlying
operating system. The
type
defines how and what is stored in the column; for example,
we've seen that it can be set to
CHAR
for strings,
SMALLINT
for numbers, or
TIMESTAMP
for
a date and time.
If you specify
NOT NULL
, a row isn't valid without a value for the column; if you specify
NULL
or omit the clause, a row can exist without a value for the column. If you specify
a
value
with the
DEFAULT
clause, it'll be used to populate the column when you don't
otherwise provide data; this is particularly useful when you frequently reuse a default
value such as a country name. The
value
must be a constant (such as
0
,
"cat"
, or
20060812045623
), except if the column is of the type
TIMESTAMP
. Types are discussed in
detail later in this section.
The
NOT NULL
and
DEFAULT
features can be used together. If you specify
NOT NULL
and
add a
DEFAULT
value, the default is used when you don't provide a value for the column.
Sometimes, this works fine:
mysql>
INSERT INTO artist SET artist_name = "Duran Duran";
Query OK, 1 row affected (0.05 sec)
And sometimes it doesn't:
mysql>
INSERT INTO artist SET artist_name = "Bob The Builder";
ERROR 1062 (23000): Duplicate entry '0' for key 1
Whether it works or not is dependent on the underlying constraints and conditions of
the database: in this example,
artist_id
has a default value of
0
, but it's also the primary
key. Having two rows with the same primary-key value isn't permitted, and so the
second attempt to insert a row with no values (and a resulting primary-key value of
0
)
fails. We discuss primary keys in detail later in this section.
Column names have fewer restrictions than database and table names. What's more,
they're not dependent on the operating system: the names are case-insensitive and
portable across all platforms. All characters are allowed in column names, though if
you want terminate them with whitespace or include periods (or other special charac-
ters such as the semicolon), you'll need to enclose the name with a backtick symbol
(
`
) on either side. We recommend that you consistently choose lowercase names for
developer-driven choices (such as database, alias, and table names) and avoid charac-
ters that require you to remember to use backticks. We also recommend being de-
scriptive with your choices:
name
doesn't mean much outside of the context of the
artist
table, but
artist_name
has universal meaning across the
music
database. We like
using the underscore character to separate words, but that's just a matter of style and
