SQL Naming and Statement Rules
SQL has rules for:
• Object names
• Regular and delimited identifiers
• Statement terminators
Object Naming Rules
The rules for naming database objects (such as tables, columns, views, and database procedures) are as follows:
• Names can contain only alphanumeric characters and must begin with an alphabetic character or an underscore (_). Database names must begin with an alphabetic character, and cannot begin with an underscore.
• Case significance (upper or lower) is determined by the settings for the database in which the object is created (Ingres or ANSI/ISO Entry SQL-92-compliant) and differs for
regular and delimited identifiers (see
Regular and Delimited Identifiers).
• Names can contain (but cannot begin with) the following special characters: 0 through 9, #, @, and $. Names specified as delimited identifiers (in double quotes) can contain additional special characters.
• Database objects cannot begin with the letters, ii. This name is reserved for use by the DBMS Server.
• The maximum length of names for the following objects is 256 bytes:
Table
Column
Partition
Procedure
Procedure parameter
Rule
Sequence
Synonym
Object
Constraint
In an installation that uses the UTF8 or any other multi-byte character set, the maximum length of a name may be less than 256 bytes because some glyphs use multiple bytes.
• The maximum length of names for the following objects is 32 bytes:
Database
Owner
User
Group
Profile
Role
Schema
Location
Event
Alarm
Node
Objects managed by the Ingres interfaces such as Query-By-Forms, Report-By-Forms, Vision, and Visual Forms Editor (for example: Forms, JoinDefs, QBFnames, Graphs, Reports)
Database names must be unique to 24 bytes (or the maximum file name length imposed by your operating system, if less than 24).
• The maximum length of a name for a collation sequence is 64 bytes.
• Avoid assigning reserved words as object names. A list of reserved words can be found in the appendix “Keywords.”
• For ANSI/ISO Entry SQL-92 compliant databases, the maximum length of an object name is 18 bytes.
User Names
User names used to install and administer Ingres can contain the following ASCII characters only: alphanumeric, at (@), pound (#), dollar ($), underscore (_), hyphen (-), period (.). Administrative user names must begin with an alphanumeric character (unless the name is delimited) and cannot contain a blank character.
Non-administrative user names can contain any characters specific to the installation character set. User names that contain characters outside this range must be delimited.
Regular and Delimited Identifiers
Identifiers in SQL statements specify names for the following objects:
• Authorization identifier (user, group, or role)
• Column
• Constraint
• Correlation name
• Cursor
• Database event
• Database procedure
• Database procedure label
• Database procedure parameter
• Database procedure variable
• Index
• Location
• Prepared query
• Rule
• Savepoint
• Schema
• Synonym
• Table
• View
The name for any of these objects can be specified using regular (unquoted) identifiers or delimited (double-quoted) identifiers. For example:
• Table name specified using a regular identifier in a SELECT SQL statement:
SELECT * FROM employees
• Table name specified using a delimited identifier in a SELECT SQL statement:
SELECT * FROM "my table"
Delimited identifiers let you use special characters in object names. The use of special characters in regular identifiers is restricted. For example, a view name that begins with or consists only of numeric characters must be delimited because a regular identifier cannot begin with the characters 0 through 9, #, @, and $.
Case Sensitivity of Identifiers
Case sensitivity for regular and delimited identifiers is defined by configuration parameters. At the time a database is created, the current setting of those parameters is stored with the database. By default, delimited identifiers are not case sensitive.
The DBMS Server treats database, user, group, role, cursor, and location names without regard to case. Mixed-case database or location names cannot be created.
To determine the settings for the database to which a session is connected
Use the
DBMSINFO function (see
DBMSINFO Function), as follows:
SELECT DBMSINFO('DB_NAME_CASE')
and
SELECT DBMSINFO('DB_DELIMITED_CASE')
Restrictions on Identifiers
The following table lists the restrictions for regular and delimited identifiers (the names assigned to database objects):
The following characters cannot be embedded in object names using either regular or delimited identifiers:
DEL (ASCII 127 or X'7F')
To specify double quotes in a delimited identifier, repeat the quotes.
For example:
"""Identifier""Name"""
is interpreted as:
"Identifier"Name"
Trailing spaces are deleted from object names specified using delimited identifiers.
For example:
CREATE TABLE "space test " (scolumn INT);
creates a table named, space test, with no trailing blanks (leading blanks are retained).
If an object name composed entirely of spaces is specified, the object is assigned a name consisting of a single blank. For example, the following creates a table named “ ”.
CREATE TABLE " " (scolumn INT);
Comment Delimiters
To indicate comments in interactive SQL, use the following delimiters:
• /* and */
For example:
/* This is a comment */
The /*...*/ delimiters allow a comment to continue over more than one line. For example:
/* Everything from here...
...to here is a comment */
• --
The -- delimiter indicates that the rest of the line is a comment. The comment cannot be continued to another line.
For example:
--This is a comment.
Comments in Embedded SQL
To indicate comments in embedded SQL, use the following delimiters:
• --, with the same usage rules as interactive SQL.
• Host language comment delimiters. For more information, see the Embedded SQL Companion Guide.
Statement Terminators
Statement terminators separate one SQL statement from another.
In interactive SQL, the statement terminator is the semicolon (;). Terminate statements with a semicolon when entering two or more SQL statements before issuing the go command (\g) or issuing another terminal monitor command.
In the following example, semicolons terminate the first and second statements. The third statement does not need to be terminated with a semicolon, because it is the final statement.
SELECT * FROM addrlst;
SELECT * FROM emp
WHERE fname = 'john';
SELECT * FROM emp
WHERE mgrname = 'dempsey'\g
If only one statement is entered, the statement terminator is not required. For example, the following single statement does not require a semicolon:
SELECT * FROM addrlst\g
In embedded SQL applications, the use of a statement terminator is determined by the rules of the host language. For details, see the Embedded SQL Companion Guide.