Friday, October 30, 2015

Database schema naming for Vendor Neutrality

database vendor-agnostic identifier limitations Oracle SQL Server PostgreSQL HSQL MySQL table column

It's surprisingly not a common topic of dicussion, but if you want to author database scripts in a way that is vendor-agnostic the consensus seems to be to conform to ANSI SQL. Are there any guides for how to name the schema identifiers to comply with the various vendor's (character limit) limitations?

I couldn't find a good cross-vendor summary, so here's an attempt at looking at the important identifier limitations across some vendors.

Vendor Identifier Limit (characters) Notes
Oracle 30 30 30
PostgreSQL 63 63 63
MS SQL Server 128 128 128
MySQL 64 64 64
HSQL 128 128 128
DB2 30 * 30 * 18 *
*Limit increased to 128 from v9.5

If you are concerned with complying with all the above vendors, then the lowest common denominator here is Oracle at 30 characters for table/column & constraints.