Case-sensitivity

Handling case-sensitivity with different database engines.

Most database engines have case-sensitive object identifiers. In most cases, when you do not specify identifiers in double quotes, the SQL parser automatically converts names to uppercase or lowercase, so that the identifiers match if the objects are also created without double quoted identifiers.

CREATE TABLE Customer ( cust_ID INTEGER )

In ORACLE, this statement would create a table named "CUSTOMER" with a "CUST_ID" column.

This table shows the behavior of each database engine regarding case sensitivity and double quoted identifiers:

Table 1. Database server support of case sensitivity and double-quoted identifiers
Database Server Type	Un-quoted names	Double-quoted names
IBM^® DB2^® UDB	Converts to uppercase	Case sensitive
IBM Informix^® (1)	Converts to lowercase	Syntax disallowed (non-ANSI mode)
Microsoft™ SQL Server (2)	Not converted, kept as is	Case sensitive
MySQL	Not converted, kept as is	Syntax disallowed
Oracle Database Server	Converts to uppercase	Uppercase
PostgreSQL	Converts to lowercase	Lowercase
Sybase ASE	Converts to lowercase	Lowercase
SQLite	Not converted, kept as is	Case insensitive

(1) If not ANSI database mode.

(2) When case-sensitive charset/collation used.

Take care with database servers marked in red, because object identifiers are case sensitive and are not converted to uppercase or lowercase if not delimited by double-quotes. This means that, by error, you can create two tables with a similar name:

CREATE TABLE customer ( cust_id INTEGER )  -- first table 
CREATE TABLE Customer ( cust_id INTEGER )  -- second table

It is recommended to design databases with lowercase table and column names.