SQL portability / Naming database objects |
Handling case-sensitivity with different database engines.
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:
Database Server Type | Un-quoted names | Double-quoted names |
---|---|---|
Genero db | Converts to uppercase | Case sensitive |
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.
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.