[GLLUG] use SQL reserved words as field names

[Charles Ulrich]

Caleb Cushing wrote:
> I'm trying to create a field named show inside of a table. however it 
> gives me a syntax error because it's a reserved word. I'm using mysql 
> 4.1.  No frontend. I've checked this 
> http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html but it 
> doesn't seem to help. I changed the name to shows and it works so I'm 
> sure it has something to do with it being a keyword. This isn't THAT 
> important but I feel it's something I should know how to do or at least 
> know why it won't work.

You can't use "show" for a column name in SQL for the same reason you 
can't use "if", "for", or "case" for variable names in many programming 
languages. It's a reserved word (as listed in the page that you linked 
to) and is prohibited from use because the parser specifically looks for 
it when processing the SQL statement so that it knows what to do with it.

When running into a reserved word situation, the best solution is to 
find a synonym for the word you really want to use. Or in the worst 
case, prefix or suffix the word with some qualifier, like "horse_show". 
Using "shows" may be suboptimal since it's very close to the reserved 
word and doesn't fit in too well with your current column-naming scheme 
in that it's the only plural column name in the table.

-- 
Charles Ulrich
Ideal Solution, LLC -- http://www.idealso.com