Amazing world about data

Developer, ThoughtWorker, Data Architect and Data Specialist

Usage of mixed case database object names is dangerous

Do not used mixed case database object names

2 minutes read

Some versions back, Oracle would not allow to create database object names with mixed cases, even if we tried to create them, we could not. In newer versions of Oracle we can create tables, columns, indexes etc using mixed case or lower case, when the names are put inside double quotes. For example

CREATE TABLE "Customer" (
"CustomerID" number(10)
...
);

CREATE INDEX "IDX_Customer_CustomerID"
on "Customer"("CustomerID");

We created table named “Customer” with a column “CustomerID” and the index is named “IDX_Customer_CustomerID”. In the above example we can see that mixed case or lower case is supported and the table, column and index are created in the database. When these names are used, we have to reference them everywhere using the lower case letters. The following statement would be invalid.

SELECT * FROM Customer

While the below statement is valid.

SELECT * FROM "Customer"

We can even create a table such as “CusTomer” and it would be valid.

CREATE TABLE "CusTomer" (
"CustomerID" number(10)
...
);

With the large amount of work involved in matching the case for the name of the database object, every time a DML/DDL statement is used against a database object, the confusion it creates when duplicate database objects are allowed to be created (“Customer” and “CusTomer” are valid names).

We can see that its better to avoid using database objects with mixed case names.

Recent posts

Post

See more

Categories

About