3

Can I use reserved keywords as column names in Derby? I'm trying to migrate database schema into Derby for testing purposes. For that reason I don't really want to change the schema structure (column names etc).

So the question is, how can I create table with column name "open" in Derby? As for example table: 

create table test ( open integer );

Tried to quote the column name, but so far no success...

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "open" at line 1, column 21.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
Toni
  • 338
  • 3
  • 10
  • @MahmoudGamal: note that bot SQL Server and MySQL also support standard quoting using double quotes `"` - which I'd always prefer over the non-standard notation using the brackets or those dreaded backticks. –  Nov 13 '12 at 09:05

1 Answers1

8

The standard way of quoting reserved words (and Derby follows the standard as nearly all DBMS do in that regard) is to use double quotes. 

create table test ( "OPEN" integer );

But beware that once you do that column (or table) names become case-sensitive. "OPEN" is a different column than "open" or "Open".

I would strongly suggest you do not use names that require you to quote them.

  • Ok, I swear that I did try that half a dozen times, and it did not work. But now it works... I'm writing tests for some legacy database, and can't really use the original DBMS in this case. But thanks! – Toni Nov 13 '12 at 08:46
  • @a_horse_with_no_name - Didn't know about that standard notation. Thanks. By the way, I know a few of these SQL standard stuff from books, But I can't find any book that covers the standard sql in more details (other than mere mortals which covers the basics and I think I read that in it) as a reference book. The standard ansi documentations as far as I know are expensive to buy. Do you know any book or articles that covers it in more details. – Mahmoud Gamal Nov 13 '12 at 09:17
  • Hmm, this did not work for me at all in Derby 10.8.3.0 CREATE TABLE MyTable ("DESC" VARCHAR(25) NOT NULL, ...) fails complaining about "DESC" – David Bradley Feb 08 '18 at 20:36