R – Do you check your field- and table names against reserved word lists

database-designreserved-words

I've sometimes had a problem with my field-, table-, view- oder stored procedure names.
Example:

   SELECT from, to, rate FROM Table1

The Problem is that from is a reserved word in SQL-92.
You could put the fieldname in double quotes to fix this, but what if some other db tools wants to read your database?
It is your database design and it is your fault if other applications have problems with your db.

There are many other reserved words (~300) and we should avoid all of them.
If you change the DBMS from manufacturer A to B, your application can fail, because a some fieldnames are now reserved words.
A field called PERCENT may work for a oracle db, but on a MS SQL Server it must be treated as a reserved word.

I have a tool to check my database design against these reserved words ; you too?

Here are my rules

  1. don't use names longer than 32 chars (some DBMS can't handle longer names)
  2. use only a-z, A-Z, 0-9 and the underscore (:-;,/&!=?+- are not allowed)
  3. don't start a name with a digit
  4. avoid these reserved words

Best Answer

Easy way: just make sure every field name is quoted.

Edit: Any sensible DB tool worth its salt should be doing the same thing, I have certainly never encountered any problems (outside of my own code, at least!)

Related Topic