Friday, October 30, 2015

Database schema naming for Vendor Neutrality

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

Problem:
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?

Solution:
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
TABLE COLUMN CONSTRAINT
Oracle 30 30 30
https://docs.oracle.com/database/121/SQLRF/sql_elements008.htm#SQLRF51129
PostgreSQL 63 63 63
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
MS SQL Server 128 128 128
https://msdn.microsoft.com/en-us/library/ms175874.aspx
MySQL 64 64 64
https://dev.mysql.com/doc/refman/5.0/en/identifiers.html
HSQL 128 128 128
http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_table_creation
DB2 30 * 30 * 18 *
*Limit increased to 128 from v9.5

https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.wn.doc/doc/c0051391.html

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.