LAST EDIT (MAYBE): I think you're trying to do this: ALTER TABLE carmake CHANGE country country ENUM('Italy', 'Germany', 'England', 'USA', 'France', 'South Korea', 'Australia', 'Spain', 'Czech Republic', 'Sweden', 'Malaysia') DEFAULT NULL The table has 'USA', 'India' and several others. The new ENUM definition only allows 'Sweden' and 'Malaysia'. YET ANOTHER EDIT: Ok, I now see that you definitely have values in the table that are not in the new ENUM. Is it STRICT_TRANS_TABLES or STRICT_ALL_TABLES? That could lead to an error, rather than the usual warning MySQL would give you in this situation. What is the output of the following command? SELECT DISTINCT country FROM carmake ĪNOTHER EDIT: What is the output of the following command? SHOW VARIABLES LIKE 'sql_mode' I suspect you have some values in your country column that do not appear in your ENUM. You may have hundreds of countries which would make for a rather large and awkward enum.ĮDIT: Now that I can see your error message: ERROR 1265 (01000): Data truncated for column 'country' at row 1. I would actually recommend a country table rather than enum column. `country` enum('Sweden','Malaysia') default NULLįWIW this would also work: ALTER TABLE carmake MODIFY COLUMN country ENUM('Sweden','Malaysia') Mysql> ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia') `country` enum('Canada','United States') default NULL Here is my test case: mysql> CREATE TABLE carmake (country ENUM('Canada', 'United States')) SELECT DISTINCT country FROM carmake OUTPUT: +-+ ) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1 | `country` enum('Japan','USA','England','Australia','Germany','France','Italy','Spain','Czech Republic','China','South Korea','India') DEFAULT NULL, `carmake_id` tinyint(4) NOT NULL AUTO_INCREMENT, SHOW CREATE TABLE OUTPUT: mysql> SHOW CREATE TABLE carmake The country column is the ENUM-type column in the above-statement. The error I get is: ERROR 1265 (01000): Data truncated for column 'country' at row 1. Here's my attempt: ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia') What is the correct MySQL syntax to achieve this? ![]() I have an ENUM-type column of country names that I need to add more countries to. In that case, a FK would be better but the base table would have to have 'unknown' as a default type and define game_rating as NOT NULL.The MySQL reference manual does not provide a clearcut example on how to do this. OK game_rating may be a bad example since ESRB could create more game ratings. you can allow for NULL (Example : game_rating ENUM('EC','E','E10+','T','M','A') NULL This allows a new game to be entered into a table without a default rating).Otherwise, bait-and-switch methods of maintenance would have to accompany the use of the ENUM. its portability must always be based on logcial dumps of the table, never physical.it will never likely experience redefinition.it must represent a type that is never changed.the type you are representing is local to the table only.Here is what should be considered about ENUM usage: Hence, this would not be suitable for ENUMs because the constraint checking would have to be done in your application rather than in the database. Such concerrns would definitely apply if using ENUM.įoreign Key Constraints would server to protect a row from absorbing invalid types. In addition, you would not be worried about those values matching up exactly with the base table definition of the type. Just by adding new entries into a table with the types needed, keys can be passed around to other tables without worrying about the underlying value being properly represented in the foreign tables. Using foreign keys makes a great deal of sense when you know that the types you are establishing can be extended, reduced, or altered. ![]() While there are bait-and-switch tricks you can perform to extend a range for an ENUM, those same tricks become very cumbersome (maybe even impossible) if you want to do other things such as reducing or altering an ENUM range. Once those values are put in place, it can be rather challenging to perform any kind of simple maintenance should you want to redefine the ENUM range. When using ENUM to have a range of values, you have to properly plan what values you will use.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |