Page 408 - Beginning PHP 5.3

Part III: Using PHP in Practice
The UNIQUE Constraint

You ’ ve already seen how you can use the keywords PRIMARY KEY to create an index on a column that
uniquely identifies each row in a table. The UNIQUE constraint is similar to PRIMARY KEY in that it
creates an index on the column and also ensures that the values in the column must be unique. The main
differences are:

❑ You can have as many UNIQUE keys as you like in a table, whereas you can have only one
primary key
❑ The column(s) that make up a UNIQUE key can contain NULL values; primary key columns
cannot contain NULL s
In the members table, you add UNIQUE constraints for the username and emailAddress columns
because, although they ’ re not primary keys, you still don ’ t want to allow multiple club members to have
the same username or email address.

You can also create a unique key for a column (or columns) by using the keywords UNIQUE KEY at the
end of the table definition. So:

CREATE TABLE members (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(30) BINARY NOT NULL UNIQUE,
...
emailAddress VARCHAR(50) NOT NULL UNIQUE,
...
PRIMARY KEY (id)
);

has exactly the same effect as:
CREATE TABLE members (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(30) BINARY NOT NULL,
...
emailAddress VARCHAR(50) NOT NULL,
...
PRIMARY KEY (id),
UNIQUE KEY (username),
UNIQUE KEY (emailAddress)

);

The ENUM Data Type
You briefly looked at ENUM columns when learning about data types in the last chapter. An ENUM
(enumeration) column is a type of string column where only predefined string values are allowed in the
field. For the members table, you created two ENUM fields:

gender ENUM( ‘m’, ‘f’ ),
favoriteGenre ENUM( ‘crime’, ‘horror’, ‘thriller’, ‘romance’, ‘sciFi’,

‘adventure’, ‘nonFiction’ ),

370

9/21/09 9:11:57 AM
c13.indd 370
c13.indd 370 9/21/09 9:11:57 AM