Preventing Duplicates from Occurring in a Table
Problem
You want to prevent a table from ever containing duplicates, so that you won’t have to worry about eliminating them later.
Solution
Use a PRIMARY KEY or a
UNIQUE index.
Discussion
To make sure that records in a table are unique, some column or
combination of columns must be required to contain unique values in
each row. When this requirement is satisfied, you can refer to any
record in the table unambiguously using its unique identifier. To
make sure a table has this characteristic, include a
PRIMARY KEY or
UNIQUE index in the table structure when you
create the table. The following table contains no such index, so it
would allow duplicate records:
CREATE TABLE person
(
last_name CHAR(20),
first_name CHAR(20),
address CHAR(40)
);To prevent multiple records 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,
it’s also necessary to declare the indexed columns
to be NOT NULL, because a
PRIMARY KEY does not allow
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 record into the table that duplicates an existing record in the column or columns that define the index. Recipe 14.3 discusses how to handle such errors or modify MySQL’s duplicate-handling behavior.
Another ...