You wrote CREATE TABLE owners and Oracle's catalog, your IDE and your schema diagram all say OWNERS. Nothing is wrong: Oracle follows the SQL standard and folds unquoted identifiers to upper case, PostgreSQL folds them to lower case, MySQL's behaviour depends on the file system and a server setting, and SQL Server compares names according to the database collation. Once you know the rules, the fix is simple: write lowercase snake_case, never quote identifiers in DDL, and expect each engine to report names its own way.
Two kinds of identifiers#
SQL distinguishes regular identifiers, written bare (owners, first_name), from delimited identifiers, written in double quotes ("Owners"). A regular identifier is case-insensitive and is stored in a canonical case chosen by the engine. A delimited identifier is stored exactly as written and is case-sensitive from then on: if you create "Owners", only "Owners" finds it, and owners, OWNERS and "owners" all refer to something else.
The standard says the canonical case for regular identifiers is upper case. Oracle, Db2 and a few others do that. PostgreSQL deliberately deviates and folds to lower case. Everybody agrees that quoting preserves case, which is why quoting is where the trouble starts.
Engine by engine#
| Engine | Unquoted identifier | Quoted identifier | Notes |
|---|---|---|---|
| Oracle | Folded to UPPER CASE | Kept as written, case-sensitive | Standard behaviour; 128-byte limit in current versions, 30 bytes in old ones |
| PostgreSQL | Folded to lower case | Kept as written, case-sensitive | 63-byte limit; longer names are truncated with a notice |
| MySQL / MariaDB | Table and database names: depends on lower_case_table_names; column and index names: case-insensitive | Backticks quote, case still governed by the setting | Setting is fixed when the data directory is initialized |
| SQL Server | Stored as written, compared per collation | Brackets or double quotes, same rule | Default collations are case-insensitive (_CI_) |
| SQLite | Stored as written, compared case-insensitively (ASCII) | Same | Quoting does not make names case-sensitive |
Oracle#
Every unquoted name lands in the data dictionary in upper case: ALL_TABLES.TABLE_NAME holds OWNERS, ALL_TAB_COLUMNS.COLUMN_NAME holds FIRST_NAME. Queries keep working in any case because the parser folds them too. The only way to get a lowercase name into the dictionary is to quote it, and then every future reference must quote it identically, including references generated by ORMs, reporting tools and migration frameworks. Most Oracle teams simply never quote.
PostgreSQL#
The mirror image: CREATE TABLE Owners creates owners. People coming from other tools sometimes quote to keep CamelCase, and then spend years typing "Owners" in every query. Some ORMs quote by default, which is how a Java entity named PetOwner becomes a table you cannot reference without quotes. Configure the naming strategy to emit snake_case instead.
MySQL and MariaDB#
Databases and tables historically mapped to directories and files, so their case sensitivity followed the file system. The server setting lower_case_table_names controls it: 0 stores and compares names as written (the default on Linux, case-sensitive), 1 stores them in lower case and compares case-insensitively (the default on Windows), and 2 stores as written but compares in lower case (the default on macOS). Since MySQL 8.0 the value must be chosen when the server is initialized and cannot be changed afterwards. Column, index and stored routine names are case-insensitive regardless. A schema that works on a developer's macOS can therefore fail on a Linux server if the code mixes Owners and owners.
SQL Server#
Names are stored as written, and whether Owners equals owners depends on the database's collation. The usual defaults are case-insensitive, so most SQL Server code is written in whatever case the author liked and it works. Move that database to a case-sensitive collation and it stops working. Pick a convention anyway; see the schema and naming rules in SQL Server.
SQLite#
Identifiers are case-insensitive for ASCII letters, quoted or not, and are stored as typed. It is the most forgiving engine here and, for that reason, the worst place to discover case problems that will appear elsewhere.
How this shows up in tools and diagrams#
Any tool that reads the catalog, rather than your source files, reports names the way the engine stores them. That includes JDBC metadata, information_schema queries, IDE schema browsers and schema visualizers. Run the same Flyway migration on PostgreSQL and Oracle and the two catalogs describe the same tables as owners.first_name and OWNERS.FIRST_NAME. When you generate an ER diagram from migrations, the Oracle diagram is in upper case because Oracle is in upper case.
Migration tools have their own settings that interact with this. Liquibase's objectQuotingStrategy decides whether it quotes everything, only reserved words or nothing, and switching it mid-project changes which names later changeSets can find. Flyway passes your SQL through untouched, so what you write is what you get. ORMs that generate DDL usually apply a naming strategy first; check what it emits before the first migration is generated, because renaming later means a migration on every environment.
Practical rules#
- Use lowercase snake_case for every table, column, index and constraint name, on every engine. It is the only convention that survives Oracle's upper-casing, PostgreSQL's lower-casing and MySQL's file-system dependence unchanged in meaning.
- Never quote identifiers in DDL. If a name needs quoting to exist, choose a different name. Reserved words are the usual reason;
orderbecomesordersorpurchase_order. - Keep names short enough for the strictest engine you might target. Old Oracle limits are 30 bytes; PostgreSQL is 63.
- Pin
lower_case_table_namesdeliberately in MySQL and MariaDB, and use the same value in Docker, CI and production. - Compare schemas case-insensitively when checking for drift across engines, and read
OWNERSin an Oracle report as the table you calledowners.
Conventions for the names themselves, plural versus singular, prefixing foreign keys and the like, are a separate discussion: see database naming conventions.
Where SchemaDrawer fits#
SchemaDrawer runs your migration files against a throw-away database of the engine you choose and draws what the catalog reports, tables, columns and keys, as an SVG diagram. On Oracle that diagram shows OWNERS, PETS and FIRST_NAME; on PostgreSQL the same files produce owners, pets and first_name. That is not a normalization choice in the tool; it is the engine's own answer, which makes the diagram a faithful record of what a migration really creates.
Key takeaways#
- Unquoted names are case-insensitive and folded: upper case in Oracle, lower case in PostgreSQL.
- Quoted names keep their case and become case-sensitive forever; avoid them in DDL.
- MySQL table-name case depends on
lower_case_table_names; SQL Server depends on collation; SQLite ignores case. - Catalog-based tools show the stored case, so an Oracle diagram in upper case is correct.