Do you check field and table names on reserved word lists?

Question

I sometimes had problems with my stored procedure names field-, table-, view-oder. Example:

SELECT from, to, rate FROM Table1 

The problem is that of is a reserved word in SQL-92. You can put the field name in double quotes to fix this, but what if some other db tools want to read your database? This is your database design and it is your mistake 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 may crash because some field names are reserved words. A field named PERCENT may work for db oracle, but on MS SQL Server it should be considered as a reserved word.

I have a tool to test my database design for these reserved words; you too?

Here are my rules

• do not use names longer than 32 characters (some DBMSs cannot handle longer names)
• use only az, AZ, 0-9, and underscore (: -;, / &! =? + - not allowed)
• do not start the name with a number
• avoid these reserved words

Answer 1

Easy way: just make sure each field name is specified.

Edit: any reasonable DB tool worthy of its salt should do the same, of course, I never came across any problems (at least outside my own code!)

Answer 2

You should not use reserved words as column names in a table, even if you can quote them.

Quoting from them can make the code very inconvenient, since you should always avoid the quote character in your SQL statements in your code. It also makes the SQL command line a real PITA, in my opinion.

In the end it looks dirty. It is much better to take the time to come up with another word that does not interfere with SQL keywords.

Your rules look good to me.

Answer 3

Sure. For this purpose, I have a table SQL_RESERVED_WORDS.

Oracle can only process 30 BTW symbol table names. And they are all uppercase.

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

Answer 4

Just avoid the reserved words.

Note that most databases (and base levels) have a way to programmatically list all reserved words. You can use this as a health check when you run the application to make sure that you are not out of the way.

The quote works, so you can do it for security. However, this makes life inconvenient for database administrators and people who create custom reports against your application, so they should only be used as band aid.

Answer 5

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

• You will not need to use quotation marks (or square brackets in the MS world), which significantly impair readability.

  Note: Readability can be especially damaged when you need to generate SQL code from SQL (the so-called "dynamic SQL") or from other languages. You don't need extra double quotes inside single quotes or extra duplicate double quotes or escaped quotes or any other obscure things like this.

  For example, how do you like these fragments:

   -- 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" 

• Most 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 in paragraph 2 on the suitability of reserved words. In the OPs example, it uses "to", "from" and "rate". The immediate question is in my mind, and therefore, perhaps, in the future of the future developer - "To whom and from what?". Perhaps rename these columns to "EffectiveFromDate" and "EffectiveUntilDate" if that is what they represent.

</ 2c>