[GLLUG] MYSQL FIELD NAMES

[Ex Fed]

Caleb 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.
>
>
>mysql> create table Races
>     -> (track CHAR(30) NOT NULL,
>     -> racedate DATE NOT NULL,
>     -> race INTEGER NOT NULL CHECK (race > 0),
>     -> win CHAR(30) NOT NULL REFERENCES Horses(horsename),
>     -> place CHAR(30) NOT NULL REFERENCES Horses (horsename),
>     -> show CHAR(30) NOT NULL REFERENCES Horses(horsename),
>     -> PRIMARY KEY (track, racedate, race));
>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
>that corresponds to your MySQL server version for the right syntax to use
>near 'show CHAR(30) NOT NULL REFERENCES Horses(horsename),

Some other issues that I've come across with field names and the code that 
accesses them are with naming a field with a numeric as the first character 
of the field name, MYSQL might let you do that, but the other code that you 
write in C++, PHP or PERL might have an issue with such a field name.  If 
you are going to dump to an XML file, then you might have trouble with that 
fieldname.  It is probably better to stay away from using reserved words or 
field names that start with a numeric.

Lee