Thread: Use of enum()

    #1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,806
    Rep Power
    529

    Use of enum()


    Previously, when I had a table, and when I wanted to ensure that a column would be limited to a few given values, I would create another table (record_status in the example below) and put a foreign constraint on the primary table (records in the below example).

    I have recently learned about enum(). Should I use enum instead? Are there times when one solution is better than the other? For instance, if I have only one table that has the record_status_id, maybe enum is better, but if I have 10 tables that contain record_status_id, maybe using another table is best?

    Thank you

    Code:
    CREATE  TABLE IF NOT EXISTS record_status (
      id CHAR(8) NOT NULL ,
      PRIMARY KEY (id) )
    ENGINE = InnoDB;
    
    CREATE  TABLE IF NOT EXISTS record (
      id INT NOT NULL ,
      data VARCHAR(45) NULL ,
      record_status_id CHAR(8) NOT NULL ,
      PRIMARY KEY (id) ,
      INDEX fk_record_record_status_idx (record_status_id ASC) ,
      CONSTRAINT fk_record_record_status
        FOREIGN KEY (record_status_id )
        REFERENCES record_status (id )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    INSERT INTO record_status (id) VALUES ('active');
    INSERT INTO record_status (id) VALUES ('inactive');
    INSERT INTO record_status (id) VALUES ('error');
    INSERT INTO record_status (id) VALUES ('deleted');
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,924
    Rep Power
    1045
    Hi,

    you use an ENUM type when you have a very small set of isolated values which you'll never change. If you may add a new status in the future or store additional information, this type is not appropriate.

    And, yes, duplicating the values for 10 tables is a problem. Also be careful if you run MySQL in non-strict mode. An invalid value will be stored as an empty string.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,806
    Rep Power
    529
    Thanks Jacques,

    I always use strict mode (learned my lesson doing differently!).

    When do you absolutely know whether you will not never, never later change?

    Do you ever use ENUM ?

    Please, others, pipe up as well.

    Thanks
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,924
    Rep Power
    1045
    Originally Posted by NotionCommotion
    When do you absolutely know whether you will not never, never later change?
    If you don't know, then don't use ENUM.

    Something like the gender usually has a definite set of values which are unlikely to change: "male", "female", "other". But that's really the only example I can think of right now.



    Originally Posted by NotionCommotion
    Do you ever use ENUM ?
    Very rarely. I wouldn't use it for a status field. Last time we did that at our company, we later changed the values.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by NotionCommotion
    Please, others, pipe up as well.
    if you google "ENUM is the spawn of the devil" i am sure you will find several of my posts

    in fact, at least one of them is right here on devshed -- http://forums.devshed.com/mysql-help...ld-544855.html

    the FK approach is by far the better choice
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,806
    Rep Power
    529
    Thank you Gentlemen,

    Jacques convinced me 99% not to use it, and Rudy sealed the deal.

IMN logo majestic logo threadwatch logo seochat tools logo