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.
