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

Question

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

Answer 1

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!)

Answer 2

Definitely. I have a SQL_RESERVED_WORDS table for that very purpose.

Oracle can only handle 30 character table names BTW. And they're all upper case.

It only takes an hour of so of unnecessary debugging before the table pays for itself.

Answer 3

You shouldn't use reserved words as column names in a table, even if you can quote them away.

Quoting them can make code really awkward as you have to escape the quote character all the time in your SQL statements within your code. It also makes the SQL command line a real PITA, in my opinion.

In the end it just looks messy. Far better to spend the time to think up of a different word that doesn't clash with SQL keywords.

Your rules look fine to me.

Answer 4

Just avoid reserved words.

Note that most databases (and database link-layers) have a way of programmatically listing all reserved words. You can use that as a sanity-check on application startup to ensure you haven't run astray.

Quoting does work, so you could do that for safety. However this makes life really awkward for DBAs and people making custom reports against your app, so that should be used as a band-aid only.

Answer 5

Putting aside obvious confusions between names and reserved words, I think there are at least two very strong reasons to avoid using reserved words as names:

1. You would not have to use quotes (or square braces in MS world) that substantially hurt readability.

   NB: Readability may be especially damaged when you find yourself in need to generate SQL code from SQL (so-called "dynamic SQL" approach) or from other languages. You do not want extra double quotes inside single quotes, or extra repeated double quotes, or escaped quotes, or any other obscure stuff like that.

   For example, how would you like snippets like these:

   -- SQL -----------------------
   declare @sql as varchar(4000)
   set @sql = 'select "To", "From" from MyTable'
   
   
   ' VB -------------------------
   Dim sql as String
   sql = "select ""To"", ""From"" from MyTable"
   
   
   // C++ -----------------------
   String sql = "select \"To\", \"From\" from MyTable"
   

2. Most of the reserved words are bad candidates for naming tables, columns, variables, etc. anyway. In the vast majority of cases nouns (sometimes adjectives) are much, much better for names than verbs, adverbs, and prepositions. :-)

Answer 6

I agree with Yarik's 2nd point about the suitability of reserved words. In the OPs example, he uses "to", "from" and "rate". The immediate question in my mind, and therefore possibly in that of a future developer is "To and from what?" Maybe consider renaming these columns to "EffectiveFromDate" and "EffectiveUntilDate", if that's what they represent.

</2c>