0

I have a column in my database table named UID. For some reason queries fail unless I surround the column name with double quotation marks (" "). None of the other columns require these quotation marks.

For example, this doesn't work:
SELECT user_name FROM user_table WHERE UID = '...'
But this does:
SELECT user_name FROM user_table WHERE "UID" = '...'

Is UID some kind of keyword? Why is it only happening to that column? How do I know if I need to use double quotes for other columns?

By the way, I'm running JDK 1.8_221 and using an oracle JDBC driver if that makes a difference.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Hannah B
  • 109
  • 1
  • 9
  • 3
    Please tag with proper database platform. Assuming it is maybe oracle? Then UID is a reserved word - https://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm – OldProgrammer Aug 25 '20 at 15:12

3 Answers3

2

Yes, it is about keywords. You can double quote everything (tables, columns) to avoid this but I can understand you don't want to do this.

To have a list of standard keywords: SQL Keywords But you can see UID is not in this list as I assume it is a reserved keyword by your database implementation. I had the same problem with a table called "order" as it contains orders. ORDER is a keyword so I had to quote it each time.

So best is to test your statements using a SQL client tool.

Since you mention Oracle: Oracle keywords: "You can obtain a list of keywords by querying the V$RESERVED_WORDS data dictionary view."

Conffusion
  • 4,335
  • 2
  • 16
  • 28
  • 4
    Enclosing object names in double quotes also makes them case sensitive, allows whitespace, and allows a wider range of characters to be used. – Mark Rotteveel Aug 25 '20 at 15:19
  • Perhaps `ORDERS` would have made a better name ;) – William Robertson Aug 25 '20 at 18:01
  • 1
    @William I never use plurals for table names but maybe that's more religion then science (https://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names) – Conffusion Aug 26 '20 at 08:07
  • Back when I started out I learned that entities are singular (Doctor/Patient) and tables are plural (DOCTORS/PATIENTS). As a side effect, this tends to avoid keywords. However, the tide of convention seemed to turn in the 90s, and now everyone states the opposite as being correct (regarding tables at least - I don't think anyone knows what an ERM is any more). – William Robertson Aug 26 '20 at 08:21
1

Yes, it is keywords and return

UID returns an integer that uniquely identifies the session user (the user who logged on).

By default, Oracle identifiers (table names, column names, etc.) are case-insensitive. You can make them case-sensitive by using quotes around them when creating them (eg: SELECT * FROM "My_Table" WHERE "my_field" = 1). SQL keywords (SELECT, WHERE, JOIN, etc.) are always case-insensitive.

You can use it for more information here.

Farshid Shekari
  • 2,391
  • 4
  • 27
  • 47
1

If your create table command for user_table looks something like this:

create table user_table ("UID" varchar2(10))

then you will have to use quotes around UID in your query. This query:

select * from user_table where UID = 'somestring'

means to use the Oracle predefined UID pseudo column and your table's UID column will not be accessed.

If your table doesn't have a user-defined UID column, then using "UID" should fail.

My guess is your table does indeed have a UID column and when you say it "doesn't work" without using the quotes you probably mean it motivates an ORA-1722.

The type of failure, when using UID without quotes, depends on the content of the string 'somestring'. If the content of that string can be cast as a number then you probably won't get the rows you expect. If it cannot be cast as a number then you'll get an ORA-1722.

As an aside, if you try to execute this, then you'll get an ORA-904:

create table user_table (UID number)
Jeff Holt
  • 2,940
  • 3
  • 22
  • 29