Database Reference
In-Depth Information
CREATE
TABLE
person
(
last_name
CHAR
(
20
),
first_name
CHAR
(
20
),
address
CHAR
(
40
)
);
To prevent multiple rows with the same first and last name values from being created
in this table, add a
PRIMARY
KEY
to its definition. When you do this, the indexed columns
must be
NOT
NULL
, because a
PRIMARY
KEY
prohibits
NULL
values:
CREATE
TABLE
person
(
last_name
CHAR
(
20
)
NOT
NULL
,
first_name
CHAR
(
20
)
NOT
NULL
,
address
CHAR
(
40
),
PRIMARY
KEY
(
last_name
,
first_name
)
);
The presence of a unique index in a table normally causes an error to occur if you insert
a row into the table that duplicates an existing row in the column or columns that define
the index.
Recipe 16.2
discusses how to handle such errors or modify MySQL's duplicate-
handling behavior.
Another way to enforce uniqueness is to add a
UNIQUE
index rather than a
PRIMARY
KEY
to a table. The two types of indexes are similar, but a
UNIQUE
index can be created on
columns that permit
NULL
values. For the
person
table, it's likely that you'd require both
the first and last names to be filled in. If so, you still declare the columns as
NOT
NULL
,
and the following table definition is effectively equivalent to the preceding one:
CREATE
TABLE
person
(
last_name
CHAR
(
20
)
NOT
NULL
,
first_name
CHAR
(
20
)
NOT
NULL
,
address
CHAR
(
40
),
UNIQUE
(
last_name
,
first_name
)
);
If a
UNIQUE
index does happen to permit
NULL
values,
NULL
is special because it is the
one value that can occur multiple times. The rationale for this is that it is not possible
to know whether one unknown value is the same as another, so multiple unknown values
are permitted.
Of course, you might want the
person
table to reflect the real world, in which people
do sometimes have the same name. In this case, you cannot set up a unique index based
on the name columns, because duplicate names must be permitted. Instead, each person
must be assigned some sort of unique identifier, which becomes the value that distinā
guishes one row from another. In MySQL, it's common to accomplish this by using an
AUTO_INCREMENT
column: