Does the SQL-1992 standard restrict naming identifiers to 18 characters

identifiernaming conventionsql-standard

In this draft of SQL:1992, I found…

Section 5.2

Leveling Rules

….

  1. The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions:

    a) No <regular identifier> or <delimited identifier body> shall contain more than 18 <character representation>s.

…and…

Annex A.1

  1. Subclause 5.2, "<token> and <separator>":

    a) In conforming Intermediate SQL language, a <regular identifier> or a <delimited identifier body> shall not comprise more than 18 <character representation>s.

    b) A <delimiter token> shall not be a <bit string literal> or a <hex string literal>.

    c) Conforming Intermediate SQL language shall contain no <identifier body> that ends in an <underscore>.

For implementors of SQL, or for users of SQL?

I searched the document multiple times, but could never get a sense of the meaning of identifier.

  • Is the spec referring to labeling within an implementation of SQL (such as PostreSQL or Oracle)? So labels used within the SQL product cannot contain identifiers that are over 18 characters or end in an underscore.

  • Or is the spec referring to users of a SQL implementation? So we who are naming our schemas, tables, columns, and other such database objects cannot use names over 18 characters or end in an underscore?

Best Answer

Unless you're implementing SQL-1992's "Full SQL" (which is the 1992 spec), you should not have identifiers with more than 18 characters.

SQL 1992 defines three levels of compatibility,

  • Entry SQL includes the statements for defining schemas, data ma- nipulation language, referential integrity, check constraints, and default clause from ISO/IEC 9075:1989, and options for module lan- guage and embedded SQL interfaces to seven different programming languages, as well as direct execution of the data manipulation statements. It also includes features related to deprecated fea- tures from ISO/IEC 9075:1989 (commas and parentheses in parameter lists, the SQLSTATE parameter, and renaming columns in the ), features related to incompatibilities with ISO/IEC 9075:1989 (colons preceding s, WITH CHECK OPTION constraint clarifications), and aids for transitioning from ISO/IEC 9075:1989 to this International Standard (s). Finally, it contains changes to correct defects found in ISO/IEC 9075:1989 (see Annex F, "Maintenance and interpretation of SQL").
  • Intermediate SQL includes major new facilities such as statements for changing schemas, dynamic SQL, and isolation levels for SQL- transactions. It also includes multiple-module support and cascade delete on referential actions, as well as numerous functional en- hancements such as row and table expressions, union join, character string operations, table intersection and difference operations, simple domains, the CASE expression, casting between data types, a diagnostics management capability for data administration and more comprehensive error analysis, multiple character repertoires, interval and simplified datetime data types, and variable-length character strings. It also includes a requirement for a flagger facility to aid in writing portable applications.
  • Full SQL increases orthogonality and includes deferred constraint checking and named constraints. Other technical enhancements in- clude additional user options to define datetime data types, self-referencing updates and deletes, cascade update on referen- tial actions, subqueries in check constraints, scrolled cursors, character translations, a bit string data type, temporary tables, additional referential constraint options, and simple assertions.

At the top you're quoting "Entry SQL" which though stricter is a "low" level of SQL conformance with the 1992 spec. This mode was for backwards compatibility with SQL:89 and the spec is pretty explicit

... [boring stuff] aids for transitioning from ISO/IEC 9075:1989 to this International Standard (s).

While I couldn't personally dig up the SQL-89 standard, I assume it does have an 18 character limit on identifiers.

The other part is alarming to me, because I believe this shouldn't apply to Intermediate SQL from the spirit of the spec which is defined as,

But none the less, the spec is the letter of the law. Clearly even in Intermediate SQL the <regular identifier> and <delimited identifier body> should not be more than 18 characters.

Related Question