Identifier Types

This topic describes the different types of SQLIdentifiers that are used in this manual. .

Complete syntax, including information about case sensitivity and special character usage in SQL Identifier types, is found in the SQL Identifier Syntax topic in this section.

We use a number of different identifier types in the SQL Reference Manual, all of which are SQLIdentifiers. Some can be qualified with schema, table, or correlation names, as described in the following table:

Topic Description
Authorization Identifier

An Authorization Identifier is an SQLIdentifier that represents the name of the user when you specify one in a connection request, otherwise known as a user name. When you connect with a user name, that name becomes the default schema name; if you do not specify a user name in the connect request, the default user name and schemaName is SPLICE.

User names can be case-sensitive within the authentication system, but they are always case-insensitive within Splice Machine's authorization system unless they are delimited.

column-Name

A column-Name is a SQLIdentifiers that can be unqualified simple-column-Names. or can be qualified with a table-name or correlation-name.

See the Column Name Notes section below for information about when a column-Name can or cannot be qualified.

column-Position

A column-Position is an integer value that specifies the ordinal position value of the column. The first column is column 1.

column-Name-or-Position

A column-Name-or-Position is either a column-Name or column-Position value.

constraint-Name

A constraint-Name is a simple SQLIdentifier used to name constraints.

You cannot qualify a constraint-Name.

correlation-Name

correlation-Name is a simple SQLIdentifier used in a FROM clause as a new name or alias for that table.

You cannot qualify a correlation-Name, nor can you use it for a column named in the FOR UPDATE clause, as described in the Correlation Name Notes section below

index-Name

An index-Name is an SQLIdentifier that can be qualified with a schemaName.

If you do not use a qualifying schema name, the default schema is assumed. Note that system table indexes are qualified with the SYS. schema prefix.

new-Table-Name

A new-Table-Name is a simple SQLIdentifier that is used when renaming a table with the RENAME TABLE statement.

You cannot qualify a new table name with a schema name, because the table already exists in a specific schema.

RoleName

A RoleName is a simple SQLIdentifier used to name roles in your database.

You cannot qualify a role name.

schemaName

A schemaName is used when qualifying the names of dictionary objects such as tables and indexes.

The default user schema is named SPLICE if you do not specify a user name at connection time, SPLICE is assumed as the schema for any unqualified dictionary objects that you reference.

Note that you must always qualify references to system tables with the SYS. prefix, e.g. SYS.SYSROLES.

simple-column-Name

A simple-column-Name is used to represent a column that is not qualified by a table-Name or correlation-Name, as described in the Column Name Notes section below.

synonym-Name

synonym-Name is an SQLIdentifier used for synonyms.

You can optionally be qualify a synonym-Name with a schemaName. If you do not use a qualifying schema name, the default schema is assumed.

table-Name

A table-Name is an SQLIdentifier use to name tables.

You can optionally qualify a table-Name with a schemaName. If you do not use a qualifying schema name, the default schema is assumed. Note that system table names are qualified with the SYS. schema prefix.

triggerName

A triggerName is an SQLIdentifier used to name user-defined triggers.

You can optionally qualify a triggerName with a schemaName. If you do not use a qualifying schema name, the default schema is assumed.

view-Name

A view-Name is an SQLIdentifier used to name views.

You can optionally qualify a view-Name with a schemaName. If you do not use a qualifying schema name, the default schema is assumed.

Column Name Notes {#Note.ColumnName}

Column names are either simple-column-Name identifiers, which cannot be qualified, or column-Name identifiers that can be qualified with a table-Name or correlation-Name. Here’s the syntax:

[ { table-Name | correlation-Name } . ] SQLIdentifier

In some circumstances, you must use a simple-column-Name and cannot qualify the column name:

Correlation Name Notes {#Note.CorrelationName}

You cannot use a correlation name for columns that are listed in the FOR UPDATE list of a SELECT. For example, in the following:

SELECT Average AS corrCol1, Homeruns AS corrCol2, Strikeouts   FROM Batting    FOR UPDATE of Average, Strikeouts;
  • You cannot use corrColl1 as a correlation name for Average because Average is listed in the FOR UPDATE list.
  • You can use corrCol2 as a correlation name for HomeRuns because the HomeRuns column is not in the update list.