< Day Day Up > |
ENUMApplying the ENUM option to a column helps guarantee that any data placed into the column is one of a predefined set of values. In the case of the missing_luggage table you saw earlier, suppose that you want to add a new column to track the reason for the luggage's mysterious disappearance. You also need to restrict the reason to any one of a specific list of causes instead of letting users enter whatever they feel like. This is a good time to use the ENUM option: CREATE TABLE missing_luggage ( missing_luggage_id INT AUTO_INCREMENT PRIMARY KEY, luggage_id INT NOT NULL, missing_reason ENUM ('Airline error', 'Late checkin', 'Possible theft', 'Unknown'), ... ... FOREIGN KEY (luggage_id) REFERENCES checked_luggage(luggage_id) ON DELETE CASCADE ) ENGINE = INNODB; Should a user or application attempt to place an invalid value in the missing_reason column, MySQL blocks the incorrect entry and places a NULL in the column. Be aware that MySQL sorts ENUM columns based on the order in which you entered your values in the column definition. Consequently, be certain to place your ENUM values in alphabetical order. Finally, although this book does not focus on relational database theory best practices, sharp-eyed readers will spot a violation in the preceding table creation example. To those readers: Please excuse this unpleasant rule infringement in the interest of simplicity and clarity for our examples. |
< Day Day Up > |