MySQL enum option

Cork Skate

ENUM is proprietary sql and not portable to other databases

ENUM is difficult to manage (requires ALTER TABLE privilege)

most instances require relational integrity so should be a foreign key instead

ENUM is silly for two values

ENUM is confusing and can easily waste developer time

for example, here are some excerpts from the mysql documentation itself --

If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a 'normal' empty string by the fact that this string has the numerical value 0. More about this later.

If an ENUM is declared NULL, NULL is also a legal value for the column, and the default value is NULL. If an ENUM is declared NOT NULL, the default value is the first element of the list of allowed values

Values from the list of allowable elements in the column specification are numbered beginning with 1.

The index value of the empty string error value is 0. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;

If you store a number into an ENUM, the number is treated as an index, and the value stored is the enumeration member with that index. (However, this will not work with LOAD DATA, which treats all input as strings.) It's not advisable to store numbers in an ENUM string because it will make things confusing.

So from that i'm guessing that the SET and ENUM options aint the best !?!!?


Is there any other option to restrict the values that are entered into one column ??

Cork Skate

Any takers ??

Cork Skate

Just update this .....


Instead of using the ENUM option,

ENUM (option1,option2,option3,option4)

i will create another table and use indexes values to solve this problem (I am using InnoDB)

CREATE TABLE MainTable( 
ID  tinyint not null  PRIMARY KEY, 
option varchar(12) not null 
) TYPE=InnoDB;
COMMIT;


insert into MainTable values
( 1,'option1' ),
( 2,'option2' ),
( 3,'option3' ),
( 4,'option4' );
COMMIT;

and then the following in the create table to refer to it.

Option  tinyint not null, 
INDEX option_index (option), 
CONSTRAINT OptionChoice_check FOREIGN KEY (option) REFERENCES ManiTable(ID)

Hope that helps someone !!