ENUM

 < Day Day Up > 

ENUM

Applying 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 > 


    MySQL Database Design and Tuning
    MySQL Database Design and Tuning
    ISBN: 0672327651
    EAN: 2147483647
    Year: 2005
    Pages: 131

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net