Naming Tables and Columns






Naming Tables and Columns

Users can select names for columns and tables. SQL has only the following restrictions:

  • Two tables belonging to the same database may not have the same name.

  • Two columns in a table may not have the same name.

  • The length of the name of a table or column is restricted. The maximum length differs from product to product. Sometimes, it is 18 characters, and sometimes, names of 128 characters are allowed.

  • A name may consist of only letters, digits, and the special symbols _ and $.

  • The name must begin with a letter.

  • Table and column names may not be reserved words; Appendix A, "Syntax of SQL," includes a list of all reserved words.

The restrictions imposed by the last two rules can be avoided by placing double quotes in front of and after the table name. The table names SELECT and FAMOUS PLAYERS are incorrect, but "SELECT" and "FAMOUS PLAYERS" are correct. However, this means that everywhere these table names are used, the double quotes must be included.

Portability

By default, MySQL does not use double quotes around names. To set this to the double quotes, the SQL_MODE system parameter must be set to ANSI_QUOTES:

SET SQL_MODE='ANSI_QUOTES'


Defining sensible names for tables and columns is extremely important. Column and table names are used in almost every statement. Awkward names, especially during interactive use of SQL, can lead to irritating mistakes, so observe the following naming conventions:

  • Keep the table and column names short but not cryptic (so PLAYERS instead of PLYRS).

  • Use the plural form for table names (so PLAYERS instead of PLAYER) so that statements "flow" better.

  • Do not use information-bearing names (so PLAYERS instead of PLAYERS_2, where the digit 2 represents the number of indexes on the table); if this information were to change, it would be necessary to change the table name together with all the statements that use the table.

  • Be consistent (PLAYERNO and TEAMNO instead of PLAYERNO and TEAMNUM).

  • Avoid names that are too long (so STREET instead of STREETNAME).

  • As much as possible, give columns with comparable populations the same name (so PLAYERNO in PLAYERS, PLAYERNO in TEAMS, and PLAYERNO in PENALTIES).

To prevent potential problems, avoid words that have a special meaning within the operating system, such as CON and LPT.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows