An identifier is defined as a sequence of one or more characters forming a unique name.
Identifiers are constructed according to certain fixed rules. It is useful to distinguish between SQL identifiers, which are local to SQL statements and host identifiers, which relate to the host programming language.
Rules for constructing host identifiers may vary between host languages.
SQL identifiers consist of a sequence of one or more Unicode characters. The maximum length of an SQL identifier is 128 characters.
SQL identifiers (except for delimited identifiers) must begin with a character having the Unicode property "ID_Start" or one of the special characters
#, and may then contain characters having the Unicode property "ID_Continue". For a detailed description, see http://www.unicode.org/reports/tr31.
The case of letters in SQL identifiers is not significant, not even if it is a delimited identifier.
Delimited identifiers means identifiers enclosed in double quotation marks:
"". Such identifiers are special in two aspects:
- They can contain characters normally not supported in SQL identifiers.
- They can be identical to a reserved word.
Two consecutive double quotation marks within a delimited identifier are interpreted as one double quotation mark.
Unicode Delimited Identifiers
A Unicode delimited identifier consists of a sequence of Unicode characters enclosed in double quotation marks and preceded by the letter
Uand an ampersand, i.e.
U&. Unicode characters can be given by four hexadecimal digits preceded by a backslash character (
\), or by six hexadecimal digits preceded with a backslash character and a plus character (
Two consecutive backslash characters within a Unicode delimited identifier are interpreted as a single backslash character.
A Unicode delimited identifier is typically used when an identifier contains a character difficult to type using the keyboard. For example the identifier
Münchencan be given as
The following examples illustrate the general rules for forming SQL identifiers:
COLUMN+1 is an expression 14 is an integer literal MODULE is a reserved word Unicode delimited identifier for München.
Note: Leading blanks are significant in delimited identifiers.
Objects in the database may be divided into two classes:
System objects, such as databanks, idents, schemas and shadows, are global to the system. System object names must be unique within each object class since they are common to all users. System objects are uniquely identified by their name alone.
Private objects, such as domains, functions, indexes, modules, precompiled statements, procedures, sequences, synonyms, tables, triggers, and views, belong to a schema and have names that are local to that schema. In a given schema, the names used for tables, synonyms, views, indexes and constraints must be unique within that group of objects, i.e. a table cannot have a name that is already being used by a synonym, view, index or constraint etc. Similarly, in a given schema, the names used for domains must be unique within that group of objects.
Functions and procedures may have the same name as long as they differ with regard to the number of parameters or the data type of the parameter. See Mimer SQL Programmer's Manual, Parameter Overloading.
The names of all other objects, modules and sequences in the schema must be unique within their respective object-type. Two different schemas may contain objects of the same type with the same name. Private objects are uniquely identified by their qualified name (see below).
Qualified Object Names
Names of private objects in the database may always be qualified by the name of the schema to which they belong. The schema name is separated from the object name by a period, with the general syntax:
If a qualified object name is specified when an object is created, it will be created in the named schema. If an object name is unqualified, a schema name with the same name as the current ident is assumed.
It is recommended that object names are always qualified with the schema name in SQL statements, to avoid confusion if the same program is run by different Mimer SQL idents.
When the name of a column is expressed in its unqualified form it is syntactically referred to as a
When the name of a column must be expressed unambiguously it is generally expressed in its fully qualified form, i.e.
table.column, and this is syntactically referred to as a
It is possible for a
column-referenceto be the unqualified name of a column in contexts where this is sufficient to unambiguously identify the column.
When the name of a column is used to indicate the column itself, e.g. in
CREATE TABLEstatements, a
column-namemust be used, i.e. the name of the column cannot be qualified.
The exception to this is in the
COMMENT ON COLUMNstatement where a
column-referenceis required because the name of the column must be qualified by the name of the table or view to which it belongs.
The contexts where the name of a column refers to the values stored in the column are:
In these contexts a
column-referencemust be used to identify the column.
The column name qualifiers which may be used in a particular SQL statement are determined by the way the table is identified in the
FROMclause of the
Alternative names (correlation names) may be introduced in the
FROMclause, and the table reference used to qualify column names must conform to the following rules:
- If no correlation names are introduced:SELECT BOOKADM.HOTEL.NAME, ROOMS.ROOMNO FROM BOOKADM.HOTEL JOIN ROOMS ...
but notSELECT BOOKADM.HOTEL.NAME, ROOMS.ROOMNO FROM HOTEL JOIN BOOKADM.ROOMS ...
- If a correlation name is introduced:SELECT H.NAME, ROOMS.ROOMNO FROM HOTEL H JOIN ROOMS ...
but notSELECT HOTEL.NAME, ROOMS.ROOMNO FROM HOTEL H JOIN ROOMS ...
In some constructions where subselects are used in search conditions, see The SELECT Expression, it may be necessary to refer in the lower level subselect to a value in the current row of a table addressed at the higher level.
A reference to a column of a table identified at a higher level is called an outer reference. The following example shows the outer reference in bold type:SELECT NAME FROM HOTEL WHERE EXISTS (SELECT * FROM FROM BOOK_GUEST WHERE HOTELCODE = HOTEL.HOTELCODE)
The lower-level subselect is evaluated for every row in the higher level result table. The example selects the name of every hotel with at least one entry in the BOOK_GUEST table.
A qualified column name is an outer reference if, and only if, the following conditions are met:
- The qualified column name is used in a search condition of a subselect.
- The qualifying name is not introduced in the
FROMclause of that subselect.
- The qualifying name is introduced at some higher level.
- The qualified column name is valid everywhere in a subselect.
Host identifiers are used in SQL statements to identify objects associated with the host language such as variables, declared areas and program statement labels.
Host identifiers are formed in accordance with the rules for forming variable names in the particular host language, see the Mimer SQL Programmer's Manual, Appendix A, Host Language Dependent Aspects.
Host identifiers are never enclosed in delimiters and may coincide with SQL reserved words.
The length of host identifiers used in SQL statements may not exceed 128 characters, even if the host language accepts longer names.
Whenever the term
host-variableappears in the syntax diagrams, one of the three following constructions must be used::host-identifier1
or:host-identifier1 INDICATOR :host-identifier2
Host-identifier1is the name of the main host variable.
Host-identifier2is the name of the indicator variable, used to signal the assignment of a null value to the host variable. See the Mimer SQL Programmer's Manual, Indicator Variables, for a description of the use of indicator variables.
The colon preceding the host identifier serves to identify the variable to the SQL compiler and is not part of the variable name in the host language.
A target variable is an item that may be specified as the object receiving the result of an assignment or a
SELECT INTO. The objects that may be specified where a target variable is expected differ depending on whether the context is Procedural usage or Embedded usage. For more information, see Usage Modes.
In the syntax diagrams, replace the term
target-variable, with the following construction:
For more information, see: DECLARE VARIABLE, CREATE FUNCTION and CREATE PROCEDURE.
Note: A routine-variable may only be specified in a procedural usage context.
Reserved Words gives a list of keywords reserved in SQL statements. These words must be enclosed in double quotation marks,
"", if they are used as SQL identifiers.
ExampleSELECT "MODULE" FROM ...
This section summarizes standard compliance concerning identifiers.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40