Reason why oracle is case sensitive?

oracle database-design case-sensitive
Steve picture Steve · Sep 15, 2011 · Viewed 75.9k times · Source

Is there a reason why Oracle is case sensitive and others like SQL Server, and MySQL are not by default?

I know that there are ways to enable/disable case sensitivity, but it just seems weird that oracle differs from other databases.

I'm also trying to understand reasons for case sensitivity. I can see where "Table" and "TaBlE" can be considered equivalent and not equivalent, but is there an example where case sensitivity would actually make a difference?

I'm somewhat new to databases and am currently taking a class.

Answer

NullUserException picture NullUserException · Sep 15, 2011

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

On the other hand, string comparisons are case-sensitive (eg: WHERE field='STRING' will only match columns where it's 'STRING') by default. You can make them case-insensitive by setting NLS_COMP and NLS_SORT to the appropriate values (eg: LINGUISTIC and BINARY_CI, respectively).

Note: When inquiring data dictionary views (eg: dba_tables) the names will be in upper-case if you created them without quotes, and the string comparison rules as explained in the second paragraph will apply here.

Some databases (Oracle, IBM DB2, PostgreSQL, etc.) will perform case-sensitive string comparisons by default, others case-insensitive (SQL Server, MySQL, SQLite). This isn't standard by any means, so just be aware of what your db settings are.

Related questions

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?

Read More
Inserting values into tables Oracle SQL

I'm trying to insert values into an 'Employee' table in Oracle SQL. I have a question regarding inputting values determined by a foreign key: My employees have 3 attributes that are determined by foreign keys: State, Position, & Manager. I am …

Read More
What is cardinality in Databases?

I have been searching all over the internet but I couldn't seem to find an answer that I can understand. So kindly, if somebody could explain to me with the help of examples what is cardinality in databases? Thank you.

Read More