MySQL Workbench ENUM() Given data type contains error

[ronc0011]

I'm working with MySQL Workbench building a Database that will have multiple tables. and one of the columns needs to be an ENUM data type. And I don't seem to be able to provide the correct syntax or something. In the create table window I have selected the desired column "make' it's a cars DB and I want an ENUM type to list the various manufacturers i.e.;

 

'Ford’,‘GMC’,‘Chevorlet’,‘Chrysler’,‘Dodge’,‘Buick’,‘Toyota’,‘Volvo’,‘international’,‘Kenworth’,‘Peterbilt’,‘Mack’,‘Freightliner’,‘Isuzu’

 

 

For reasons I haven't been able to spot it keeps throwing this error.

 

"The given Data Type contains errors"

 

To avoid Type-O's I have been copy & pasting this string from a notepad file so I can double check my typing and syntax etc.

 

Is it the copy & paste that's causing the issue?

[benanamen]

one of the columns needs to be an ENUM data type

I want an ENUM type to list the various manufacturers

 Who told you to do that? Dont do it unless you want to do it wrong. The manufacturers needs to be another table.

Edited November 8, 2015 by benanamen

[ronc0011]

So that doesn't really answer the question does it?

 

Design is still in it's early stages, nothings settled or even determined.

 

At this point the only real question I have is what's up with the ENUM() data type.

 

And BTW I just tried it with typing in the string by hand one at a time i.e building the string manually. It didn't like that either.

 

I'm pretty sure I've done this before in PHPMyAdmin with less aggravation. In fact I created a column just before this one that worked just fine.

 

However, it does occur to me that the previous column had no upper case characters in it but the "Model" column has and needs to use upper case characters in it i.e. GMC etc...

[benanamen]

So you want someone to help you do it wrong? And now that you know it is wrong you still want to do it anyways? Let us know when you want to do it correctly and we will be glad to help.

 

 

nothings settled or even determined

 

 

It seems your determined to do it wrong.

Edited November 8, 2015 by benanamen

[Barand]

As Benanemen said, put the makes in a separate table, each with a unique id and store the appropriate id in the car table

carMake
+--------+-------------------+
| makeId |        make       |
+--------+-------------------+
|    1   | Ford              |
|    2   | GMC               |
|    3   | Chevorlet         |
|    4   | Chrysler          |
|    5   | Dodge             |
|    6   | Buick             |
|    7   | Toyota            |
|    8   | Volvo             |
|    9   | International     |
|   10   | Kenworth          |
|   11   | Peterbilt         |
|   12   | Mack              |
|   13   | Freightliner      |
|   14   | Isuzu             |
+--------+-------------------+
     |
     |
     +-----------------+
                       |
           car         |
           +-------+--------+----------+------+--------+
           | carId | makeId | plate    | year | colour |
           +-------+--------+----------+------+--------+
           |   1   |   2    | AB10XYZ  | 2010 | Red    |
           |   2   |  14    | M55DEF   | 2005 | Blue   |
           |   3   |   8    | DT15AAA  | 2015 | Silver |
           +-------+--------+----------+------+--------+

To retrieve the make use a query with a JOIN between the two tables, matching on the makeId.

[benanamen]

Using Barands example, you would actually do the same thing for colors. Colors would be in a separate table in the same exact way.

[ronc0011]

I really didn’t think the question was that difficult. When it’s all boiled down to its barest simplest element. The question is; “WHY is MySQL giving me errors on my ENUM() data type?”. Anyway, never mind that, I sorted it out, sort of.

I really don’t have any questions about how I’m designing my tables. In fact, designing the table is what I’m doing. For you guys edification this is not a database for something like a dealership. It is intended to be a backend for a company’s fleet maintenance schedule. So of course the company might have Company cars, local & over the road delivery trucks, vans, etc. The Web based app needs to have an admin page where a manager can enter and update new records. At the same time the drivers, mechanics or other users need to be able to update individual maintenance records

 

In any event the design part is what I’m actually doing now. In association with “make” & “model” there needs to be records for things like “oil change”, “Tire change”, “belts & hoses”, etc. then other things such as mileage per day – week, so on & so on…

In this scenario “make” ideally needs to be a multiple choice drop down selection. So let’s move on from the separate table approaches. Granted as the design process moves forward no doubt many changes will need to be made as the need makes itself apparent.

[benanamen]

@ronc0011,

All of what you just said makes absolutely NO DIFFERENCE about using enum.

 

 

So let’s move on from the separate table approaches. 

 

You seem plenty determined to do things wrong so why ask for experts help?

 

no doubt many changes will need to be made as the need makes itself apparent

 

It already has but you refuse to accept it. Apparently the company has no idea you don't know what your doing and are not willing to listen to people that know much, much more than you. I sure wouldn't want MY fleet maintained with what you are doing.

Edited November 9, 2015 by benanamen

[ronc0011]

So you just aren't or can't answer the question why MySQL is giving me issues over my ENUM()data type. Lucky for me I managed to sort it out without any help from you.

 

Or should I just accept the idea that for some weird inexplicable reason MySQL doesn't like my list of manufactures as an ENUM() data type.

[Barand]

Is it the copy & paste that's causing the issue?

 

Yes. Notepad is using non-standard single quotes. This should work

ENUM('Ford','GMC','Chevrolet','Chrysler','Dodge','Buick','Toyota','Volvo','International','Kenworth','Peterbilt','Mack','Freightliner','Isuzu')

[ronc0011]

Yes. Notepad is using non-standard single quotes. This should work

 

ENUM('Ford','GMC','Chevrolet','Chrysler','Dodge','Buick','Toyota','Volvo','International','Kenworth','Peterbilt','Mack','Freightliner','Isuzu')

 

Actually I tried that while wasting time waiting on totally useless & pointless responces from others. As it turned out I think it may have been a quirk in the way Workbench operates. I discovered that while I was entering the detail of a column such as an ENUM column, when I clicked to the next column to begine the next column expecting that Workbench would commit the entries I had just made. Instead it threw errors. It seems you absolutely must click the "Apply" button else if you click the next line where you would begin defining a new column Workbench chokes and throws an error saying that the data type you have given is not valid or something to that effect. Taking care to use the "Apply" button while also manually typing in the string does work. Oddly at one point I was typing in truncated versions of the string in question & still getting the error. Turns out it was the issue of moving to the next line in the Workbench interface that was causing the issue. This doesn't cause a problem if the data types are "VARCHAR(45)" or "INT" the default data type or INT. So actually I was just creating the columns like I had been doing all along but the ENUM() data type kept throwing this error for reasons I couldn't figure out. In the case of an ENUM() data type you must use the "Apply" button

Edited November 9, 2015 by ronc0011

[Barand]

I'm using workbench (6.3.3) and the only time it threw an error for me was when there really was an error. I just now entered another ENUM column and it let me tab quite happily to the next column name. Only had to "apply" after all changes were done.

[benanamen]

totally useless & pointless responces from others

 

So that's what you call correct expert advice huh? Your gonna go far as programmer.

 

 

Since, as you said, the enum is for a dropdown, you are already showing yet another flaw in that you are going to store the actual make names as repeated data in some table as the dropdown choice, yet another wrong programming move, but I know, you just don't care about doing anything right.

Edited November 10, 2015 by benanamen

[ronc0011]

I'm using workbench (6.3.3) and the only time it threw an error for me was when there really was an error. I just now entered another ENUM column and it let me tab quite happily to the next column name. Only had to "apply" after all changes were done.

Really? Well clearly something's causing the errors that I apparently haven't spotted yet. As I've already noted I got the string entered just as you've given and it worked fine typed directly into the interface, While the attempt immediately before that I had done exactly the same thing only I had clicked on the next column definition and it threw the error. I suppose I may have typed a period "." instead of a comma "," somewhere though I was being pretty careful about that. As I noted before I had also by this time started trying to enter the list in shorter segments i.e. just 3 or 4 names, etc.

 

I suppose I could try recreating different incorrect scenarios attempting to recreate the problem.