SQL syntax requires users to supply names for elements such as tables, views, cursors, and columns when they define them. SQL statements then use those names to refer to the table, view, or other element. In syntax diagrams, FairCom DB SQL identifiers are shown in lowercase type.
The maximum length for FairCom DB SQL identifiers is 64 characters.
There are two types of FairCom DB SQL identifiers:
- Conventional identifiers (Conventional Identifiers, /doc/sqlref/33346.htm)
- Delimited identifiers enclosed in double quotation marks
Conventional Identifiers
Unless they are delimited identifiers (refer to Delimited Identifiers), FairCom DB SQL identifiers must:
- Begin with an uppercase or lowercase letter
- Contain only letters, digits, or the underscore character ( _ )
- Not be reserved words
Except for delimited identifiers, FairCom DB SQL does not distinguish between uppercase and lowercase letters in identifiers. By default, all database object names are converted to lowercase. However, SQL statements can refer to the names in mixed case. Thus, conventional names for database objects, such as tables, columns, indexes, and so forth, are case-insensitive.
The following examples show some of the characteristics of conventional identifiers:
-- Names are case-insensitive:
CREATE TABLE TeSt (CoLuMn1 CHAR);
INSERT INTO TEST (COLUMN1) VALUES('1');
1 record inserted.
SELECT * FROM TEST;
COL
---
1
1 record selected
TABLE TEST;
COLNAME NULL ? TYPE LENGTH
------- ------ ---- ------
column1
-- Cannot use reserved words:
CREATE TABLE TABLE (COL1 CHAR);
CREATE TABLE TABLE (COL1 CHAR);
*
error(-20003): Syntax error
Delimited Identifiers
Delimited identifiers are FairCom DB SQL object names enclosed in double quotation marks (""). Enclosing a name in double quotation marks allows it to include UTF-8 characters or be a reserved SQL word. By default, the server does not preserve the case of the name, which ensures the ASCII characters in the name are case-insensitive. Subsequent references to a delimited identifier must also use enclosing double quotation marks. To include a double-quotation-mark character in a delimited identifier, precede it with another double quotation mark.
The following FairCom DB SQL example shows several ways to create and refer to delimited identifiers:
CREATE TABLE "delimited ids"
( """" CHAR(10),
"_uscore" CHAR(10),
"""quote" CHAR(10),
" space" CHAR(10) );
INSERT INTO "delimited ids" ("""") VALUES('text string');
1 record inserted.
SELECT * FROM "delimited ids";
" _USCORE "QUOTE SPACE
- ------- ------ ------
text string
1 record selected
CREATE TABLE "TABLE" ("CHAR" CHAR);