invalid identifier for creating table

Question

create table ROOM

(NO_ROOM INT PRIMARY KEY, TYPE VARCHAR2(8) NOT NULL, SIZE VARCHAR2(8) NOT NULL)

;

I get ORA-00904, i think problem is from NO_ROOM. I try to use NUMBER, it's same.

PS. I make table for room that has no.room type and size.

Answer 1

SIZE is a reserved keyword. That means we cannot use it as an identifier (unless we put it in double quotes, but double-quoted identifiers are Teh Suck! so don't do that). Change the column name to ROOMSIZE and your statement will run.

Note that TYPE is also a keyword but not reserved. So we are allowed to use it as a column identifier. Types weren't introduced until 8; making TYPE a reserved keyword would have broken code in Oracle applications all over the world, not least in its own data dictionary.

The documentation has a complete list of the reserved words. Find it here.

---

Why would using "SIZE" be such a bad idea? After all, as @JavaBeginner says, the SQL standard does permit it.

Once we choose to use double-quotes to get around Oracle's naming rules we are condemned to use them whenever we reference the column. Hence this would not be a valid query:

 select no_room, size
 from room
 where size > 10

We would have to write instead:

 select no_room, "SIZE"
 from room
 where "SIZE" > 10

And it always have to be "SIZE": "size" is a different identifier. so is "Size".

Best practice is the informed interpretation of what the standards permit. SQL allows us to do things which we shouldn't do if we want to build a robust and maintainable database. Not using double-quoted identifiers falls into that category.

Answer 2

Size is a keyword and it cannot be used as column name unless you use it with double quotes. My suggestion is to use some other name for column as room_size. If you still want to use SIZE as column name for some reason, you will need to use double quotes while creating the table and also take care of the same while doing any other queries using this column.

Here is the working fiddle with size used as column name http://sqlfiddle.com/#!4/7e746

I do want to add(same as above) that using reserved word for column name(using double quotes) is a bad idea.

Answer 3

You can't use any of these reserved words as identifiers: http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_keywd.htm

Size is in the list. If you choose another name for the column you should be okay.

Answer 4

SIZE is a reserved word by Oracle! So, it's not allowed to use them as a name of variables or objects. You can find here http://docs.oracle.com/cd/B28359_01/appdev.111/b31231/appb.htm list of reserved words for Oracle 11g.

Here is the second moment, you can use it inside double quote like "SIZE" or "Size", but that will be case sensitive and not recommended.