SQL Reference Guide > SQL Reference Guide > Introducing SQL > SQL Naming and Statement Rules
Was this helpful?
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.
For details about delimited identifiers, see Regular and Delimited Identifiers (see Regular and Delimited Identifiers).
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):
Restrictions
Regular Identifiers
Delimited Identifiers
Quotes
Specified without quotes
Specified in double quotes
Keywords
Cannot be a keyword
Can be a keyword
Valid special characters
“At” sign (@)
(not ANSI/ISO)
Crosshatch (#)
(not ANSI/ISO)
Dollar sign ($)
(not ANSI/ISO)
Underscore (_)
Ampersand (&)
Asterisk (*)
“At” sign (@)
Backslash (\)
Braces ({ })
Caret (^)
Colon (:)
Comma (,)
Crosshatch (#)
Dollar sign ($)
Double quotes (")
Equal sign (=)
Exclamation point (!)
Forward slash (/)
Left and right caret (< >)
Left and right parentheses
Left quote (ASCII 96 or X'60')
Hyphen (-)
Percent sign (%)
Period (.)
Plus sign (+)
Question mark (?)
Semicolon (;)
Single quote (')
Space
Tilde (~)
Underscore (_)
Vertical bar (|)
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.
Last modified date: 08/29/2024