RDB/VMS SQL CREATE — VMS RDB_4.1A
Creates definitions for VAX SQL.
Additional information available:
CATALOGCOLLATING_SEQUENCEDATABASEDOMAININDEX
SCHEMASTORAGE_AREASTORAGE_MAPTABLETRIGGER
VIEW
CATALOG
CREATE CATALOG ──> catalog-name ───┐ ┌─────────────────────────────────┘ └─┬───────────────────────────────┬─> typebox (;) ├───> create-schema-statement ─┬┘ └─┬─> schema-element ─┬────────┘ └─────── , <────────┘
Additional information available:
More Informationcatalog nameExamples
More Information
Creates a name for a group of schemas within a multischema database. You cannot create a catalog within a database unless the database has the multischema attribute.
catalog name
catalog-name = ─┬──────> name-of-catalog ────────────┬──> │ │ └─> typebox (") ─> alias.name-of-catalog ─> typebox (") ─┘ The name of the catalog or the qualified catalog name. If you omit the catalog name when specifying an object in a multischema database, SQL uses the default catalog name RDB$CATALOG. You can use the SET CATALOG statement to change the current default catalog name in dynamic or interactive SQL.
Examples
This example attaches to a database called PERSONNEL and creates a
new catalog in that database.
SQL> ATTACH 'ALIAS CORPORATE FILENAME DISK01:[DEPT3]PERSONNEL -
cont> MULTISCHEMA IS ON';
!
!The SET ANSI QUOTING ON statement allows the use of
!double quotation marks, which SQL requires when you
!qualify a catalog name with an alias.
!
SQL> SET ANSI QUOTING ON;
SQL> CREATE CATALOG "CORPORATE.MARKETING";
!
SQL> SHOW CATALOG;
Catalogs in database PERSONNEL
"CORPORATE.MARKETING"
"CORPORATE.RDB$CATALOG"
This example shows a CREATE CATALOG clause used within an interactive
CREATE DATABASE statement. Because the new catalog is created within
the default alias, SQL does not qualify the catalog name with an
alias in SHOW statement output.
SQL> CREATE DATABASE FILENAME DISK01:[DEPT2]INVENTORY
cont> MULTISCHEMA IS ON
cont> CREATE CATALOG PARTS
cont> CREATE SCHEMA PRINTERS AUTHORIZATION HALVORSON
cont> CREATE TABLE LASER EXTERNAL NAME IS DEPT_2_LASER
cont> (SERIAL_NO INT, LOCATION CHAR)
cont> CREATE SCHEMA TERMINALS AUTHORIZATION HALVORSON
cont> CREATE TABLE VT100 EXTERNAL NAME IS DEPT_2_VT100
cont> (SERIAL_NO INT, LOCATION CHAR);
SQL> SHOW CATALOG;
Catalogs in database with filename DISK01:[DEPT2]INVENTORY
PARTS
RDB$CATALOG
SQL> show schemas;
Schemas in database with filename DISK01:[DEPT2]INVENTORY
PARTS.PRINTERS
COLLATING_SEQUENCE
CREATE COLLATING SEQUENCE sequence-name ──┐ ┌───────────────────────────────────────┘ └──┬─────────────────────────────────────┬──┐ └─>
E
X
T
E
R
N
A
L
N
A
M
E typebox (I)typebox (S) external-name ───┘ │ ┌───────────────────────────────────────────┘ └──┬────────────────────────────┬──┐ └─>
C
O
M
M
E
N
T
I
S ──> typebox (')string' ─┘ │ ┌───────────────────────────┘ └─>ncs-name ─┬───────────────────────┬─> typebox (;) └─>
F
R
O
M library-name ──┘
Additional information available:
More Information
Identifies a collating sequence other than the database default collating sequence that you plan to use with certain domains. (You must use a collating sequence that has been defined using the VMS National Character Set (NCS) utility.) The default collating sequence for a database is established by the COLLATING SEQUENCE IS clause in the CREATE DATABASE statement; if you omit that clause at database definition time, the default sequence is ASCII. You must enter a CREATE COLLATING SEQUENCE statement before you enter the name of that sequence in CREATE DOMAIN or ALTER DOMAIN statements.
Example
The following example creates a collating sequence using the
predefined collating sequence FRENCH:
SQL> CREATE COLLATING SEQUENCE FRENCH EXTERNAL NAME IS VALBONNE_SEQUENCE -
cont> COMMENT IS 'FOR COLLATING VALBONNE DATA' FRENCH;
SQL> SHOW COLLATING SEQUENCE;
User collating sequences in database with filename
DISK01:[DEPT3]PERSONNEL
FRENCH
Comment: FOR COLLATING VALBONNE DATA
SQL>
DATABASE
CREATE DATABASE ─┬────────────────┬──┐ └─> typebox (A)typebox (L)typebox (I)typebox (A)typebox (S) alias ─┘ │ ┌───────────────────────────────────┘ └┬──────────────────────────┬┬──────────────────────────┬─┐ └┬─> root-file-params-1 ─┬┘└─┬─> root-file-params-2 ┬─┘ │ └───────────────<────────┘ └──────────<───────────┘ │ ┌─────────────────────────────────────────────────────────┘ └┬──────────────────────┬─┬───────────────────────┬─────> typebox (;) └> storage-area-params ┘ └┬─> database-element ─┬┘ └──────── , <─────────┘
Additional information available:
More Informationaliasroot file params1root file params2
storage area paramsdatabase element
More Information
The CREATE DATABASE statement creates a database file, specifies its
name, and determines its physical characteristics. If you specify a
path name, CREATE DATABASE also creates a directory in the VAX Common
Data Dictionary (CDD) where definitions of other database elements
can be stored.
In its simplest form, CREATE DATABASE at least creates database
system files, specifies their names, and determines the physical
characteristics of the database. Using the optional elements of
CREATE DATABASE, you can also specify:
o Whether the database created with CREATE DATABASE is multifile or
single-file. The presence or absence of a CREATE STORAGE AREA
statement in a CREATE DATABASE statement is what determines
whether the database is single-file or multifile.
o Values for various root file parameters.
o Values for storage area parameters.
o Any number of database elements (CREATE or GRANT statements, or
CREATE STORAGE AREA clauses).
alias
Specifies the alias for the implicit database declaration executed by CREATE DATABASE. An alias is a name for a particular attachment to a database. For more information see the Help topic on aliases.
root file params1
root-file-params-1 = ─┬──────────────────────────>────────────────────┬─> ├─>
F
I
L
E
N
A
M
E file-spec ─────────────────────────┤ ├─>
P
A
T
H
N
A
M
E path-name ─────────────────────────┤ ├─>
M
U
L
T
I
S
C
H
E
M
A
I
S
O
N ──────────────────────────┤ ├─>
D
B
K
E
Y
S
C
O
P
E typebox (I)typebox (S) ─┬─>
T
R
A
N
S
A
C
T
I
O
N ─┬──────────┤ │ └─>
A
T
T
A
C
H ──────┘ │ ├─>
C
O
L
L
A
T
I
N
G
S
E
Q
U
E
N
C
E sequence-name ────────┐ │ │ ┌─────────────────────────────────────────┘ │ │ └┬───────────────┬────┬─────────────┬─────┐ │ │ └─>
C
O
M
M
E
N
T
I
S ─┘ └─> typebox (')string ' ┘ │ │ │ ┌─────────────────────────────────────────┘ │ │ └──> ncs-name ───┬───────────────────────┬───┤ │ └─>
F
R
O
M library-name ──┘ │ ├─>
N
U
M
B
E
R typebox (O)F
U
S
E
R
S ──> number-users ───────────┤ ├─>
N
U
M
B
E
R typebox (O)F
B
U
F
F
E
R
S ─────> number-buffers ────┤ ├─>
N
U
M
B
E
R typebox (O)F
V
A
X
C
L
U
S
T
E
R
N
O
D
E
S ─> number-nodes ─┤ ├─>
N
U
M
B
E
R typebox (O)F
R
E
C
O
V
E
R
Y
B
U
F
F
E
R
S ───┐ │ │ ┌─────────────────────────────┘ │ │ └───> number-buffers ───────────────────────┤ ├─>
B
U
F
F
E
R
S
I
Z
E typebox (I)typebox (S) ─> buffer-blocks ──>
B
L
O
C
K
S ─┤ └─> global-buffer-params ───────────────────────┘
Additional information available:
MULTISCHEMA_IS_ONMULTISCHEMA_IS_OFF
MULTISCHEMA_IS_ON
Specifies the multischema attribute for the database created by CREATE DATABASE. You must specify the multischema attribute for your database if you plan to create multiple schemas and store them within catalogs. Each time you attach to a database with the multischema attribute, you can specify whether you want multischema naming enabled or disabled for that connection by using the MULTISCHEMA IS clause in the DECLARE ALIAS or ATTACH statement.
MULTISCHEMA_IS_OFF
Specifies that the database created by CREATE DATABASE can only contain one schema. A single-schema database is the default.
global buffer params
global-buffer-params= ─>
G
L
O
B
A
L
B
U
F
F
E
R
S typebox (A)typebox (R)typebox (E) ─┬─>
E
N
A
B
L
E
D ──┬───┐ └─>
D
I
S
A
B
L
E
D ─┘ │ ┌─────────────────────────────────────┘ └─┬────────────────────────────────────────────────┬─> └─> ( ─>
N
U
M
B
E
R typebox (I)typebox (S) number-glo-buffers ─> , ──┐ │ ┌───────────────────────────────────────┘ │ └─>
U
S
E
R
L
I
M
I
T typebox (I)typebox (S) max-glo-buffers ────> ) ──┘
root file params2
root-file-params-2 = ─┬──────────────────────────> ───────────────────────────┬─> ├─>
S
N
A
P
S
H
O
T typebox (I)typebox (S) ────┬─────>
E
N
A
B
L
E
D ─┬─>
I
M
M
E
D
I
A
T
E ┬─┬─┤ │ │ └─>
D
E
F
E
R
R
E
D ─┘ │ │ │ └─────>
D
I
S
A
B
L
E
D ───────────>─────┘ │ ├─>
D
I
C
T
I
O
N
A
R
Y typebox (I)typebox (S) ───┬───>
R
E
Q
U
I
R
E
D ───────┬────────────┤ │ └───>
N
O
T
R
E
Q
U
I
R
E
D ───┘ │ ├─>
A
D
J
U
S
T
A
B
L
E
L
O
C
K
G
R
A
N
U
L
A
R
I
T
Y typebox (I)typebox (S) ─┬─>
E
N
A
B
L
E
D ──┬─────┤ │ └─>
D
I
S
A
B
L
E
D ─┘ │ ├─>
C
A
R
R
Y
O
V
E
R
L
O
C
K
S typebox (A)typebox (R)typebox (E) ─┬─>
E
N
A
B
L
E
D ──┬───────────────┤ │ └─>
D
I
S
A
B
L
E
D ─┘ │ ├─>
L
O
C
K
T
I
M
E
O
U
T
I
N
T
E
R
V
A
L typebox (I)typebox (S) number-seconds SECONDS ────┤ ├─>
S
E
G
M
E
N
T
E
D
S
T
R
I
N
G ─┬─> typebox (S)typebox (T)typebox (O)typebox (R)typebox (A)typebox (G)typebox (E)
A
R
E
A typebox (I)typebox (S) area-name ───┤ ├─>
L
I
S
T ─────────────┘ │ └─>
P
R
O
T
E
C
T
I
O
N typebox (I)typebox (S) ───┬───>
A
N
S
I ──┬─────────────────────┘ └───>
A
C
L
S ──┘
Additional information available:
More Information
Root file parameters are parameters that control the characteristics of the database root file associated with the database, or characteristics stored in the root file that apply to the entire database. You can specify these parameters for either single-file or multifile databases. Not all root file parameters specified in CREATE DATABASE can be changed with the ALTER DATABASE statement. To change the root file parameters that cannot be changed with the ALTER DATABASE statement, you must use the EXPORT and IMPORT statements in the RDO utility of Rdb/VMS.
LOCK_TIMEOUT_INTERVAL
Use the LOCK TIMEOUT INTERVAL parameter to specify the default number of seconds you want processes to wait during a lock conflict before timing out.
storage area params
storage-area-params = ─┬─┬──────────────────────────>─────────────────────────────┬─┬──> │ ├─>
A
L
L
O
C
A
T
I
O
N typebox (I)typebox (S) ───> number-pages ────> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ─────────┤ │ │ ├─>
P
A
G
E
S
I
Z
E typebox (I)typebox (S) ────> page-blocks ─────> typebox (B)typebox (L)typebox (O)typebox (C)typebox (K)typebox (S) ────────┤ │ │ ├─>
P
A
G
E
F
O
R
M
A
T typebox (I)typebox (S) ──┬────>
U
N
I
F
O
R
M ────┬────────────────┤ │ │ │ └────>
M
I
X
E
D ──────┘ │ │ │ ├─>
T
H
R
E
S
H
O
L
D
S typebox (A)typebox (R)typebox (E) ( val1 ─┬──────────────────────┬─> ) ─┤ │ │ │ └─> ,val2 ─┬──────────┬┘ │ │ │ │ └─> ,val3 ─┘ │ │ │ ├─>
I
N
T
E
R
V
A
L typebox (I)typebox (S) ───────> number-data-pages ──────────────┤ │ │ ├─>
E
X
T
E
N
T typebox (I)typebox (S) ────┬──> extent-pages ────> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ────┬────┤ │ │ │ └──> (extension-options)──────────┘ │ │ │ ├─>
S
N
A
P
S
H
O
T
F
I
L
E
N
A
M
E ───> file-spec ────────────────────┤ │ │ ├─>
S
N
A
P
S
H
O
T
A
L
L
O
C
A
T
I
O
N typebox (I)typebox (S) ──> snp-pages ──> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ──────┤ │ │ ├─>
S
N
A
P
S
H
O
T
E
X
T
E
N
T typebox (I)typebox (S) ─┬─> extent-pages ──> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ─┬────┤ │ │ │ └─> (extension-options) ────┘ │ │ │ └─>
W
R
I
T
E
O
N
C
E ──────────────────────────────────────────┘ │ │ │ └──────────────────────────────<─────────────────────────────┘
Additional information available:
More Informationextension options
More Information
Storage area parameters are parameters that control the
characteristics of database storage area files. You can specify most
storage area parameters for either single-file or multifile
databases, but the effect of the clauses differs:
o For single-file databases, the storage area parameters specify
the characteristics for the single storage area in the database.
o For multifile databases, the storage area parameters specify a
set of default values for any storage areas created by the CREATE
DATABASE statement that do not specify their own values for the
same parameters. The default values apply to the RDB$SYSTEM
storage area, plus any others named in CREATE STORAGE AREA
clauses.
CREATE STORAGE AREA clauses within the CREATE DATABASE statement
can override these default values.
extension options
extension-options = ──>
M
I
N
I
M
U
M typebox (O)F min-pages PAGES, ──┐ ┌────────────────────────────────┘ └>
M
A
X
I
M
U
M typebox (O)F max-pages PAGES, ──┐ ┌────────────────────────────────┘ └>
P
E
R
C
E
N
T
G
R
O
W
T
H typebox (I)typebox (S) growth ──────>
Additional information available:
More Information
Extension options are useful for exercising greater control over the size and growth of an extent or snapshot extent when the 100 page default is not desired. Extension options are particularly useful with multivolume databases. Extension options include the MIN, MAX, and PERCENT parameters. If you use the MIN, MAX, and PERCENT parameters, you must enclose them in parentheses: (MINIMUM OF min-pages PAGES, MAXIMUM OF max-pages PAGES, PERCENT GROWTH IS growth). MINIMUM OF min-pages PAGES specifies the minimum number of pages of each extent. The default is 100 pages. MAXIMUM OF max-pages PAGES specifies the maximum number of pages of each extent. The default is 10,000 pages. PERCENT GROWTH IS growth specifies the percent growth of each extent. The default is 20 percent growth.
database element
database-element = ─┬─> create-catalog-statement ────────────┬─> ├─> create-collating-sequence-statement ─┤ ├─> create-domain-statement ─────────────┤ ├─> create-index-statement ──────────────┤ ├─> create-schema-statement ─────────────┤ ├─> create-storage-area-clause ──────────┤ ├─> create-storage-map-statement ────────┤ ├─> create-table-statement ──────────────┤ ├─> create-trigger-statement ────────────┤ ├─> create-view-statement ───────────────┤ └─> grant-statement ─────────────────────┘
Additional information available:
create storage area clausestorage area paramscreate catalog statementcatalog name
create schema statementMore Information
create storage area clause
CREATE STORAGE AREA ─┬─> area-name ─┬─┬──────────────────────┬┐ └─>
R
D
B
$
S
Y
S
T
E
M ┘ └─>
F
I
L
E
N
A
M
E file-spec ┘│ ┌────────────────────────────────────────────────────────┘ └┬───────────────────────┬─> typebox (;) └─> storage-area-params ┘
Additional information available:
More Information
You cannot issue CREATE STORAGE AREA as an independent statement. It is a clause allowed as part of a CREATE DATABASE or IMPORT statement. The CREATE STORAGE AREA clause creates additional storage areas in a multifile database. A storage area is data and snapshot files that are associated with particular tables in a multifile database. A CREATE STORAGE AREA clause specifies the names for the storage area files and determines their physical characteristics. Subsequent CREATE STORAGE MAP statements actually associate the storage area with particular tables or columns of tables in the database.
storage area params
storage-area-params = ─┬─┬──────────────────────────>─────────────────────────────┬─┬──> │ ├─>
A
L
L
O
C
A
T
I
O
N typebox (I)typebox (S) ───> number-pages ────> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ─────────┤ │ │ ├─>
P
A
G
E
S
I
Z
E typebox (I)typebox (S) ────> page-blocks ─────> typebox (B)typebox (L)typebox (O)typebox (C)typebox (K)typebox (S) ────────┤ │ │ ├─>
P
A
G
E
F
O
R
M
A
T typebox (I)typebox (S) ──┬────>
U
N
I
F
O
R
M ────┬────────────────┤ │ │ │ └────>
M
I
X
E
D ──────┘ │ │ │ ├─>
T
H
R
E
S
H
O
L
D
S typebox (A)typebox (R)typebox (E) ( val1 ─┬──────────────────────┬─> ) ─┤ │ │ │ └─> ,val2 ─┬──────────┬┘ │ │ │ │ └─> ,val3 ─┘ │ │ │ ├─>
I
N
T
E
R
V
A
L typebox (I)typebox (S) ───────> number-data-pages ──────────────┤ │ │ ├─>
E
X
T
E
N
T typebox (I)typebox (S) ────┬──> extent-pages ────> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ────┬────┤ │ │ │ └──> (extension-options)──────────┘ │ │ │ ├─>
S
N
A
P
S
H
O
T
F
I
L
E
N
A
M
E ───> file-spec ────────────────────┤ │ │ ├─>
S
N
A
P
S
H
O
T
A
L
L
O
C
A
T
I
O
N typebox (I)typebox (S) ──> snp-pages ──> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ──────┤ │ │ ├─>
S
N
A
P
S
H
O
T
E
X
T
E
N
T typebox (I)typebox (S) ─┬─> extent-pages ──> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ─┬────┤ │ │ │ └─> (extension-options) ────┘ │ │ │ └─>
W
R
I
T
E
O
N
C
E ──────────────────────────────────────────┘ │ │ │ └──────────────────────────────<─────────────────────────────┘ Storage area parameters control the characteristics of database storage area files.
create catalog statement
CREATE CATALOG ──> catalog-name ───┐ ┌─────────────────────────────────┘ └─┬───────────────────────────────┬─> typebox (;) ├───> create-schema-statement ─┬┘ └─┬─> schema-element ─┬────────┘ └─────── , <────────┘
Additional information available:
More Information
Creates a name for a group of schemas within a database. You cannot create a catalog within a database unless the database has the multischema attribute. If you omit the catalog name when specifying an object in a multischema database, SQL uses the default catalog name RDB$CATALOG. You can use the SET CATALOG statement to change the current default catalog name in dynamic or interactive SQL.
catalog name
catalog-name = ─┬──────> name-of-catalog ────────────┬──> │ │ └─> typebox (") ─> alias.name-of-catalog ─> typebox (") ─┘
create schema statement
CREATE SCHEMA ─┬─> schema-name ───────────────────────┬─┐ ├─>
A
U
T
H
O
R
I
Z
A
T
I
O
N auth-id ─────────────┤ │ └─> schema-name AUTHORIZATION auth-id ─┘ │ ┌────────────────────────────────────────┘ └─┬─────────────────────────┬───────────> typebox (;) └─┬──> schema-element ──┬─┘ └──────────<──────────┘
More Information
The CREATE SCHEMA statement or clause creates a schema within a database that has the multischema attribute. The use of CREATE SCHEMA to specify physical database characteristics, as root file parameters, is deprecated syntax in Version 4.1: use the CREATE DATABASE statement instead.
Additional information available:
schema element
schema-element =
─┬─> create-storage-map-statement ────────┬─>
├─> create-collating-sequence-statement ─┤
├─> create-domain-statement ─────────────┤
├─> create-table-statement ──────────────┤
├─> create-trigger-statement ────────────┤
├─> create-index-statement ──────────────┤
├─> create-view-statement ───────────────┤
└─> grant-statement ─────────────────────┘
A schema element is any CREATE statement or a GRANT statement:
o CREATE STORAGE MAP statement
o CREATE DOMAIN statement
o CREATE TABLE statement
o CREATE INDEX statement
o CREATE VIEW statement
o GRANT statement (Granting privileges on definitions, not
database)
DOMAIN
CREATE DOMAIN ─┐ ┌──────────────┘ ├─> domain-name IS data-type ─┬───────────────────┬──┐ │ └──>default-value ──┘ │ │ ┌────────────────────────────────────────────────┘ │ └─┬─────────────────────────────────────┬────────┐ │ └─>
E
X
T
E
R
N
A
L
N
A
M
E typebox (I)typebox (S) external-name ───┘ │ │ │ │ ┌────────────────────────────────────────────────┘ │ └─┬────────────────────────────────────────────┬─┐ │ ├─>
C
O
L
L
A
T
I
N
G
S
E
Q
U
E
N
C
E typebox (I)typebox (S) sequence-name ────┤ │ │ └─>
N
O
C
O
L
L
A
T
I
N
G
S
E
Q
U
E
N
C
E ───────────────────┘ │ │ ┌────────────────────────────────────────┘ │ └──┬──────────────────────────┬────────────────┬> typebox (;) │ └─┬─>sql-and-dtr-clause ─┬─┘ │ │ └───────────<──────────┘ │ └─>
F
R
O
M ──> path-name ─┬─────────────────>───────────────┬┘ └─>
D
A
T
A
B
A
S
E typebox (A)typebox (L)typebox (I)typebox (A)typebox (S) alias ─────────┘
Additional information available:
More Informationexternal namedata typeFROM clause
default valuesql and dtr clause
More Information
Creates a domain definition, which includes the domain name, a data type, and optional SQL and DATATRIEVE support clauses. The domain name must be unique among domain names in the database. When the CREATE DOMAIN statement executes, VAX SQL adds the domain definition to the physical database. If you attached the database with the PATHNAME specification, the domain definition is also added to the data dictionary. Domains ensure that similar columns in multiple tables will comply to one standard. Once you have a defined domain, use the CREATE or ALTER TABLE statement to define many columns based on a domain definition. Domains also allow you to change the data type or SQL and DATATRIEVE parameters for all columns defined using a domain, simply by changing the domain itself.
external name
The name that you specify for a data definition when you create it is called the internal name. Each data definition also has an external name that it is known by Rdb/VMS. Except in multischema databases, the internal name and external name for each element are the same. SQL requires that, for each definition of a particular type, the internal name must be unique within the schema and the external name must be unique within the database. If you create a table named EMPLOYEES in each schema of a multischema database, SQL generates a unique external name (for each EMPLOYEES table after the first one created) by adding a serial number and truncating the name, if necessary. If you prefer to specify an external name instead of relying on SQL to generate one, you can do so using the EXTERNAL NAME IS clause for any CREATE statement.
data type
data-type = ─┬─>
C
H
A
R ─┬────────┬───────────────────────────┬──> │ └─> (n) ─┘ │ ├─>
V
A
R
C
H
A
R (n) ───────────────────────────────┤ ├─>
L
O
N
G
V
A
R
C
H
A
R ──────────────────────────────┤ ├─>
T
I
N
Y
I
N
T ──────────────┬─────┬────────┬─────┤ ├─>
S
M
A
L
L
I
N
T ─────────────┤ └─> (n) ─┘ │ ├─>
I
N
T
E
G
E
R ──────────────┤ │ ├─>
B
I
G
I
N
T ───────────────┤ │ ├─>
Q
U
A
D
W
O
R
D ─────────────┤ │ ├─>
L
I
S
T
O
F
B
Y
T
E
V
A
R
Y
I
N
G ─┘ │ ├─>
D
E
C
I
M
A
L ─┬─┬─────────────────────────────┬─┤ ├─>
N
U
M
E
R
I
C ─┘ └─> ( ──> n ─┬────────┬─> ) ──┘ │ │ └─> , n ─┘ │ ├─>
F
L
O
A
T ─┬────────┬──────────────────────────┤ │ └─> (n) ─┘ │ ├─>
R
E
A
L ──────────────────────────────────────┤ ├─>
D
O
U
B
L
E
P
R
E
C
I
S
I
O
N ──────────────────────────┤ └─> date-time-data-types ──────────────────────┘
FROM clause
The FROM clause specifies the data dictionary path name of a dictionary field definition. SQL creates the domain using the definition from this record. You can specify either a full data dictionary path name or a relative data dictionary path name. You cannot specify SQL and DATATRIEVE support clauses if you use the FROM path-name form of the CREATE DOMAIN statement.
Additional information available:
alias
The alias specifies the name for an attachment to a particular database. SQL adds the domain definition to the database referred to by the alias. If you do not specify an alias, SQL adds the domain definition to the default schema.
default value
default-value = DEFAULT ──┬──>literal ───────────┬─> ├──>
U
S
E
R ─────────────┤ ├──>
N
U
L
L ─────────────┤ ├──>
C
U
R
R
E
N
T
D
A
T
E ──────┤ ├──>
C
U
R
R
E
N
T
T
I
M
E ──────┤ └──>
C
U
R
R
E
N
T
T
I
M
E
S
T
A
M
P ─┘
Additional information available:
More InformationliteralMore Information
More Information
The default value of a column is the value stored in the database if an insert operation on a row specifies no value for that column. You can use literals, the NULL keyword, the current timestamp, or the user name as default values. If you do not specify a default value, SQL assigns NULL as the default value. If you specify a default value for a column in a table, it overrides any default value specified for the domain on which the column is based. You might have any of several possible reasons for specifying a default value for a column; for instance, you may want to store the most commonly used value, or you may want to store a value that highlights (visually displays) the fact that no value was stored.
literal
A value expression that is either numeric, character string, or date.
USER
The user name of the process that invokes interactive SQL or runs a program.
NULL
A null value. 4CURRENT_DATE DATE data type value containing year, month, and day for date 'today'. 4CURRENT_TIME TIME data type value containing hour, minute, and second for time 'now'.
CURRENT_TIMESTAMP
The date and time currently defined in Rdb/VMS.
More Information
A value to be stored in a column if the row that is inserted does not include a value for that column. You can use literals, the NULL keyword, the current timestamp, or the user name as default values. If you do not specify a default value, SQL assigns NULL as the default value.
sql and dtr clause
sql-and-dtr-clause = ─┬─>
Q
U
E
R
Y
H
E
A
D
E
R typebox (I)typebox (S) ─┬> quoted-string ──┬─────────────────┬─> │ └────── typebox (/) <────────┘ │ ├─>
E
D
I
T
S
T
R
I
N
G typebox (I)typebox (S) quoted-string ─────────────────────────┤ │ │ ├─>
Q
U
E
R
Y
N
A
M
E Ftypebox (O)typebox (R) ─┬─> typebox (D)typebox (T)typebox (R) ────────┬─> typebox (I)typebox (S) quoted-string ─┤ │ └─> typebox (D)typebox (A)typebox (T)typebox (A)typebox (T)typebox (R)typebox (I)typebox (E)typebox (V)typebox (E) ─┘ │ └─>
D
E
F
A
U
L
T
V
A
L
U
E Ftypebox (O)typebox (R) ─┬─> typebox (D)typebox (T)typebox (R) ────────┬─> typebox (I)typebox (S) literal ────┘ └─> typebox (D)typebox (A)typebox (T)typebox (A)typebox (T)typebox (R)typebox (I)typebox (E)typebox (V)typebox (E) ─┘ Optional formatting clauses allow you to modify data displays or query characteristics for interactive SQL users and DATATRIEVE users. A query header specifies a string that interactive SQL or DATATRIEVE displays in place of the column name when it retrieves values from a column. An edit string specifies a string that controls how interactive SQL or DATATRIEVE formats the display of values in a column. DEFAULT VALUE and QUERY NAME are active only from DATATRIEVE.
INDEX
CREATE ─┬───────────┬─>
I
N
D
E
X index-name ─────────────────────┐ └─>
U
N
I
Q
U
E ─┘ │ ┌─────────────────────────────────────────────────────────────┘ └──┬───────────────────────────────────┬─>
O
N table-name ─────┐ └─>
E
X
T
E
R
N
A
L
N
A
M
E typebox (I)typebox (S) external-name ─┘ │ ┌───────────────────────────────<─────────────────────────────┘ └> ( ─┬> column-name ┬───────┬─┬────────────────────────┬┬─> ) ┐ │ ├> typebox (A)typebox (S)typebox (C) ─┤ ├> typebox (S)typebox (I)typebox (Z)typebox (E) typebox (I)typebox (S) n ────────────┤│ │ │ └> typebox (D)typebox (E)typebox (S)typebox (C) ┘ └> typebox (M)typebox (A)typebox (P)typebox (P)typebox (I)typebox (N)typebox (G) typebox (V)typebox (A)typebox (L)typebox (U)typebox (E)typebox (S) l TO h ┘│ │ └───────────────────────────── , <─────────────────┘ │ ┌────────────────────────<─────────────────────────────────────┘ └┬────────────────────────────>──────────────────────────────┬─┐ └─>
T
Y
P
E typebox (I)typebox (S) ─┬─>
S
O
R
T
E
D ─┬───────────────────────────────┬┬─┘ │ │ └┬┬─>
N
O
D
E
S
I
Z
E number-bytes ─┬┬┘│ │ │ │├─>
P
E
R
C
E
N
T
F
I
L
L percentage ┤│ │ │ │ │└─>
U
S
A
G
E ─┬─>
U
P
D
A
T
E ─┬────┘│ │ │ │ │ └─>
Q
U
E
R
Y ──┘ │ │ │ │ └────────────────<────────────┘ │ │ └─>
H
A
S
H
E
D ──────────────────────────────────┘ │ ┌──────────────────────────────────────────────────────────────┘ └─┬───────────────────────┬─> typebox (;) └─> index-store-clause ─┘
Additional information available:
More Informationexternal nameSIZE IS nindex store clause
More Information
The CREATE INDEX statement defines an index for a base table. An
index allows direct access to the rows in the table to avoid
sequential searching.
You define an index by listing the columns in a table that make up
the index. You can define more than one index for a table. The
index can be either simple or multisegmented. A simple index is made
up of one column while a multisegmented index is made up of two or
more columns.
Optional arguments to CREATE INDEX let you specify:
o The characteristics of index nodes
o Whether the index is a hashed index or a B-tree index
o The names of a storage area or storage areas that will contain
the index
o Whether the index segments are created in ascending or descending
order
o Thresholds for the logical storage area that will contain the
index
external name
The name that you specify for a data definition when you create it is called the internal name. Each data definition also has an external name that it is known by to Rdb/VMS. Except in multischema databases, the internal name and external name for each element are the same. SQL requires that, for each definition of a particular type, the internal name must be unique within the schema and the external name must be unique within the database. If you create a table named EMPLOYEES in each schema of a multischema database, SQL generates a unique external name (for each EMPLOYEES table after the first one created) by adding a serial number and truncating the name, if necessary. If you prefer to specify an external name instead of relying on SQL to generate one, you can do so using the EXTERNAL NAME IS clause for any CREATE statement.
ASC
This optional keyword causes SQL to create ascending index segments. An ascending index is one in which index keys are stored in ascending sequence. If you omit the ASC or DESC keywords, ascending order is the default.
DESC
This optional keyword causes SQL to create descending index segments. A descending index is one in which index keys are stored in descending sequence. A descending index can improve the performance of queries that retrieve records with high index key values. If you omit the ASC or DESC keywords, ascending order is the default.
SIZE IS n
A compression clause for text or varying text index keys that limits the number of characters used for retrieving data. The n specifies the number of characters of the key that are used in the index. The compressed key must be specified with a DUPLICATES ARE ALLOWED clause.
MAPPING_VALUES
A compression clause for all-numeric columns that translates the
column values into a more compactly encoded form.
Numeric compression can only be done on TINYINT, word and longword
fields. No compression is allowed on BIGINT or the floating-point
data types.
You can mix mapped and unmapped columns, but the most storage space
is gained by building indexes of multiple columns of data type WORD
or LONGWORD.
The l (low) through h (high) specifies the range of integers as the
value of the index key.
For the compressed key:
o The valid range cannot be zero
o The range h through l is limited to (2**31) through 4 x
(10**scale)
index store clause
index-store-clause = STORE ─┐ ┌───────────────────────────┘ └┬─>
I
N area-name ───┬──────────────────────────────────┬───┬─> │ └─> ( ─> threshold-clause ─> ) ────┘ │ │ │ └─>
U
S
I
N
G ─> ( ─┬─> column-name ─┬─> ) ─────────────┐ │ └────────<───────┘ │ │ ┌───────────────────────────────────────────────────┘ │ └┬>
I
N area-name ─┬───────────────────────────────┬─┐ │ │ └─> ( ─> threshold-clause ─> ) ─┘ │ │ │ ┌────────────────────────────────────────────────┘ │ │ └─>
W
I
T
H
L
I
M
I
T
O
F ─> ( ─┬─> literal ─┬─> ) ──────┬───┐ │ │ └──────<─────┘ │ │ │ └─────────────────────<────────────────────────────┘ │ │ │ │ ┌────────────────────────────────────────────────────────┘ │ └──>
O
T
H
E
R
W
I
S
E typebox (I)typebox (N) area-name ─────┐ │ ┌────────────────────────────┘ │ └─┬──────────────────────────────────┬──────────────────┘ └─> ( ──> threshold-clause ───> ) ─┘
Additional information available:
threshold clauseMore Information
threshold clause
threshold-clause = ─┬─>
T
H
R
E
S
H
O
L
D ─┬─> typebox (I)typebox (S) ─┬─> ( ──> val1 ──> ) ─────┬─> │ └─> typebox (O)F ─┘ │ │ │ └─>
T
H
R
E
S
H
O
L
D
S ─┬─> typebox (A)typebox (R)typebox (E) ─┬───────────┐ │ └─> typebox (O)F ─┘ │ │ ┌──────────────────────────────────┘ │ └─> ( ──> val1 ─┬───────────────────────┬─> ) ─┘ └─> , val2 ─┬───────────┤ └─> , val3 ─┘
More Information
A storage map definition for the index. You can specify a store
clause for indexes in a multifile schema only. The STORE clause in a
CREATE INDEX statement allows you to specify which storage area files
will be used to store the index entries:
o All index entries can be associated with a single storage area.
o Index entries can be systematically distributed, or partitioned,
among several storage areas by specifying upper limits on the
values for a key in a particular storage area.
o You can specify a different SPAM threshold for each logical area
that is created by a storage map. You can specify a default
threshold for any new logical area using the THRESHOLDS ARE
clause in the CREATE INDEX statement.
To specify thresholds for a particular storage area, each storage
area mentioned in the STORE clause can be followed by a
THRESHOLDS ARE clause. The value val represents the percentage
of the partition. You can only specify three thresholds for
Rdb/VMS Version 4.1. Thresholds are not allowed for segmented
string storage areas.
If you omit the storage map definition, the default is to store all
the entries for an index in the main RDB$SYSTEM storage area.
You should define a storage area for an index that matches the
storage map for the table with which it is associated.
SCHEMA
CREATE SCHEMA ─┬─> schema-name ───────────────────────┬─┐ ├─>
A
U
T
H
O
R
I
Z
A
T
I
O
N auth-id ─────────────┤ │ └─> schema-name AUTHORIZATION auth-id ─┘ │ ┌────────────────────────────────────────┘ └─┬─────────────────────────┬───────────> typebox (;) └─┬──> schema-element ──┬─┘ └──────────<──────────┘
Additional information available:
More Informationschema nameauthorization idschema element
More Information
The CREATE SCHEMA statement used in earlier versions of SQL is deprecated syntax for V4.1, and is supported only for upward compatibility. Use the CREATE DATABASE statement instead of the CREATE SCHEMA statement to create the database root file or to specify any physical characteristics of the database. SQL generates an informational message when you issue a CREATE SCHEMA statement, unless you have enabled multischema naming (MULTISCHEMA IS ON) in a previous statement and you do not specify any physical attributes of the database. When these conditions are met, SQL creates a schema (a group of definitions) within the multischema database.
schema name
schema-name = ─┬────────────────────────────────────────┬─┐ ├──────> catalog-name ────────────┬─> . ─┘ │ ├─> typebox (") ─> alias.catalog-name ─> typebox (") ─┘ │ │ ┌──────────────────────────────────────┘ │ └──────────────> name-of-schema ────────┬─> └─> typebox (") ─> alias.name-of-schema ─> typebox (") ─────────┘
authorization id
AUTHORIZATION auth-id identifies the user who has access to a schema. For more information see the Help topic on authorization-id.
schema element
schema-element = ─┬─> create-storage-map-statement ────────┬─> ├─> create-collating-sequence-statement ─┤ ├─> create-domain-statement ─────────────┤ ├─> create-table-statement ──────────────┤ ├─> create-trigger-statement ────────────┤ ├─> create-index-statement ──────────────┤ ├─> create-view-statement ───────────────┤ └─> grant-statement ─────────────────────┘
Additional information available:
More Information
A schema element is a GRANT statement or any CREATE statement except CREATE STORAGE AREA: o CREATE STORAGE MAP statement o CREATE DOMAIN statement o CREATE TABLE statement o CREATE INDEX statement o CREATE VIEW statement o GRANT statement
STORAGE_AREA
CREATE STORAGE AREA ─┬─> area-name ─┬─┬──────────────────────┬┐ └─>
R
D
B
$
S
Y
S
T
E
M ┘ └─>
F
I
L
E
N
A
M
E file-spec ┘│ ┌────────────────────────────────────────────────────────┘ └┬───────────────────────┬─> typebox (;) └─> storage-area-params ┘
Additional information available:
More Informationexternal namestorage area params
More Information
You cannot issue CREATE STORAGE AREA as an independent statement. It is a clause allowed as part of a CREATE DATABASE or IMPORT statement. The CREATE STORAGE AREA clause creates additional storage areas in a multifile schema. A storage area is data and snapshot files that are associated with particular tables in a multifile schema. A CREATE STORAGE AREA clause specifies the names for the storage area files and determines their physical characteristics. Subsequent CREATE STORAGE MAP statements actually associate the storage area with particular tables or columns of tables in the schema.
external name
The name that you specify for a data definition when you create it is called the internal name. Each data definition also has an external name that it is known by to Rdb/VMS. Except in multischema databases, the internal name and external name for each element are the same. SQL requires that, for each definition of a particular type, the internal name must be unique within the schema and the external name must be unique within the database. If you create a table named EMPLOYEES in each schema of a multischema database, SQL generates a unique external name (for each EMPLOYEES table after the first one created) by adding a serial number and truncating the name, if necessary. If you prefer to specify an external name instead of relying on SQL to generate one, you can do so using the EXTERNAL NAME IS clause for any CREATE statement.
storage area params
storage-area-params = ─┬─┬──────────────────────────>─────────────────────────────┬─┬──> │ ├─>
A
L
L
O
C
A
T
I
O
N typebox (I)typebox (S) ───> number-pages ────> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ─────────┤ │ │ ├─>
P
A
G
E
S
I
Z
E typebox (I)typebox (S) ────> page-blocks ─────> typebox (B)typebox (L)typebox (O)typebox (C)typebox (K)typebox (S) ────────┤ │ │ ├─>
P
A
G
E
F
O
R
M
A
T typebox (I)typebox (S) ──┬────>
U
N
I
F
O
R
M ────┬────────────────┤ │ │ │ └────>
M
I
X
E
D ──────┘ │ │ │ ├─>
T
H
R
E
S
H
O
L
D
S typebox (A)typebox (R)typebox (E) ( val1 ─┬──────────────────────┬─> ) ─┤ │ │ │ └─> ,val2 ─┬──────────┬┘ │ │ │ │ └─> ,val3 ─┘ │ │ │ ├─>
I
N
T
E
R
V
A
L typebox (I)typebox (S) ───────> number-data-pages ──────────────┤ │ │ ├─>
E
X
T
E
N
T typebox (I)typebox (S) ────┬──> extent-pages ────> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ────┬────┤ │ │ │ └──> (extension-options)──────────┘ │ │ │ ├─>
S
N
A
P
S
H
O
T
F
I
L
E
N
A
M
E ───> file-spec ────────────────────┤ │ │ ├─>
S
N
A
P
S
H
O
T
A
L
L
O
C
A
T
I
O
N typebox (I)typebox (S) ──> snp-pages ──> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ──────┤ │ │ ├─>
S
N
A
P
S
H
O
T
E
X
T
E
N
T typebox (I)typebox (S) ─┬─> extent-pages ──> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ─┬────┤ │ │ │ └─> (extension-options) ────┘ │ │ │ └─>
W
R
I
T
E
O
N
C
E ──────────────────────────────────────────┘ │ │ │ └──────────────────────────────<─────────────────────────────┘ Storage area parameters control the characteristics of database storage area files.
STORAGE_MAP
CREATE STORAGE MAP map-name ──────────────────────────────┐ ┌────────────────────────────────────────────────────────┘ └┬───────────────────────────────────┬───────────────────┐ └─>
E
X
T
E
R
N
A
L
N
A
M
E typebox (I)typebox (S) external-name ─┘ │ ┌────────────────────────────────────────────────────────┘ ├>
F
O
R table-name ┬┬─────────────────────────────────┬┬──┐ │ │├─┬>
E
N
A
B
L
E ─┬>
C
O
M
P
R
E
S
S
I
O
N ──────┤│ │ │ ││ └>
D
I
S
A
B
L
E ┘ ││ │ │ │├─>
P
L
A
C
E
M
E
N
T
V
I
A
I
N
D
E
X index-name┤│ │ │ │└─> threshold-clause ─────────────┘│ │ │ └───────────────── <────────────────┘ │ │ ┌────────────────────────────────────────────────────┘ │ └> store-clause ───────────────────────────────────┬──> typebox (;) └> store-lists-clause ─────────────────────────────────┘
Additional information available:
More Informationexternal nameExamplesthreshold clause
store clausereorganize clausestore lists clause
More Information
Associates a table with one or more storage areas. CREATE STORAGE
MAP specifies a storage map that controls which rows of a table or
which lists are stored in which storage areas. You can specify
separate storage areas for the lists in one or more tables or for the
lists in certain columns of tables.
In addition to creating storage maps, CREATE STORAGE MAP has options
that control:
o Which index the database system uses when inserting rows in the
table
o Whether the rows of the table will be stored in a compressed
format
o Which columns of the table will be stored in an area
o Threshold values for storage areas with mixed format pages and
logical areas areas in storage areas with uniform format pages.
external name
The name that you specify for a data definition when you create it is called the internal name. Each data definition also has an external name that it is known by to Rdb/VMS. Except in multischema databases, the internal name and external name for each element are the same. SQL requires that, for each definition of a particular type, the internal name must be unique within the schema and the external name must be unique within the database. If you create a table named EMPLOYEES in each schema of a multischema database, SQL generates a unique external name (for each EMPLOYEES table after the first one created) by adding a serial number and truncating the name, if necessary. If you prefer to specify an external name instead of relying on SQL to generate one, you can do so using the EXTERNAL NAME IS clause for any CREATE statement.
Examples
The following example shows a list mapped to three different storage
areas. The PERSON table includes a column PHOTO that may contain
several different photos of the same employee. This requires a large
amount of data storage. The following map randomly allocates lists
for the PHOTO column across the three listed areas.
SQL> CREATE STORAGE MAP LARGE_OBJECTS
STORE LISTS
IN RDB$SYSTEM
IN FILE_AREA FOR (PERSON.RESUME)
IN IMAGE_AREA1 FOR (PERSON.PHOTO)
IN IMAGE_AREA2 FOR (PERSON.PHOTO)
IN IMAGE_AREA3 FOR (PERSON.PHOTO)
IN EMPLOYEE_AREA FOR (PERSON);
threshold clause
threshold-clause = ─┬─>
T
H
R
E
S
H
O
L
D ─┬─> typebox (I)typebox (S) ─┬─> ( ──> val1 ──> ) ─────┬─> │ └─> typebox (O)F ─┘ │ │ │ └─>
T
H
R
E
S
H
O
L
D
S ─┬─> typebox (A)typebox (R)typebox (E) ─┬───────────┐ │ └─> typebox (O)F ─┘ │ │ ┌──────────────────────────────────┘ │ └─> ( ──> val1 ─┬───────────────────────┬─> ) ─┘ └─> , val2 ─┬───────────┤ └─> , val3 ─┘
Additional information available:
More Information
You can specify up to three SPAM thresholds for storage areas with mixed format pages and logical areas in storage areas with uniform format pages. The value val represents the percentage of the partition. You can only specify three thresholds for Rdb/VMS Version 4.1. You can specify a default threshold for a new logical area using the THRESHOLDS ARE clause in the ALTER STORAGE MAP statement. To specify thresholds for a particular storage area, each storage area mentioned in the STORE clause can be followed by a THRESHOLDS ARE clause. Thresholds are not allowed for segmented string storage areas. The THRESHOLD clause only applies to new areas: you cannot change a threshold for a logical area. Although a threshold clause associated with a particular area is enclosed in parentheses, the default threshold clause for a storage map is not. (The threshold clause associated with a particular area appears within the store clause.)
store clause
store-clause = ─>
S
T
O
R
E ─┬────────────────────────────────────────┬> └┬>
I
N area-name ────┐ │ │ ┌─────────────────┘ │ │ └─┬───────────────────────────────┬───┤ │ └─> ( ─> threshold-clause ─> ) ─┘ │ │ │ ├─> across-clause ──────────────────────┤ └─> using-clause ───────────────────────┘
Additional information available:
threshold clauseMore Informationacross clauseusing clause
threshold clause
threshold-clause = ─┬─>
T
H
R
E
S
H
O
L
D ─┬─> typebox (I)typebox (S) ─┬─> ( ──> val1 ──> ) ─────┬─> │ └─> typebox (O)F ─┘ │ │ │ └─>
T
H
R
E
S
H
O
L
D
S ─┬─> typebox (A)typebox (R)typebox (E) ─┬───────────┐ │ └─> typebox (O)F ─┘ │ │ ┌──────────────────────────────────┘ │ └─> ( ──> val1 ─┬───────────────────────┬─> ) ─┘ └─> , val2 ─┬───────────┤ └─> , val3 ─┘
More Information
The STORE clause specifies the storage map definition. The STORE
clause in a CREATE STORAGE MAP statement allows you to specify which
storage area files will be used to store rows from the table:
o All rows of a table can be associated with a single storage area
o Rows of a table can be randomly distributed among several storage
areas
o Rows of a table can be systematically distributed, or
partitioned, among several storage areas by specifying upper
limits on the values for a column in a particular storage area
This is called horizontal partitioning.
o You can specify a different SPAM threshold for each logical area
that is created by a storage map, provided the areas are UNIFORM.
The value val represents the percentage of the partition. You
can only specify three thresholds for Rdb/VMS Version 4.1.
Specify a default threshold for any new logical area using the
THRESHOLDS ARE clause in the CREATE STORAGE MAP statement. To
specify thresholds for a particular storage area, each storage
area mentioned in the STORE clause can be followed by a
THRESHOLDS ARE clause. Thresholds are not allowed for segmented
string storage areas or for MIXED areas. The THRESHOLD clause
will only be applied to new areas: you cannot change a threshold
for a logical area.
If you omit the storage map definition, the default is to store all
the records for a table in the main RDB$SYSTEM storage area.
across clause
across-clause = ───> typebox (R)typebox (A)typebox (N)typebox (D)typebox (O)typebox (M)typebox (L)typebox (Y)
A
C
R
O
S
S ───┐ ┌──────────────────────┘ └─> ( ─┬> area-name ─┬────────────────────────┬┬> ) ─> │ └─> ( threshold-clause ) ┘│ └──────────────── , <───────────────────┘
using clause
using-clause = ───>
U
S
I
N
G ───> ( ─┬─> column-name ─┬─> ) ─┐ └────── , ───────┘ │ ┌───────────────────────────────────────────┘ │ └┬─>
I
N area-name ──────────────────────────┐ │ │ │ ┌───────────────────────────────────────┘ │ └─┬────────────────────────────────┬────┐ │ └──> ( ─> threshold-clause ─> ) ─┘ │ │ ┌───────────────────────────────────────┘ │ └─>
W
I
T
H
L
I
M
I
T
O
F ─> ( ─┬─ literal ─┬─> ) ─┬──┐ │ └──── , <───┘ │ │ └─────────────────────── , <──────────────────┘ │ ┌────────────────────────────────────────────────┘ └──>
O
T
H
E
R
W
I
S
E typebox (I)typebox (N) area-name ───┐ ┌───────────────────────────┘ └─┬───────────────────────────────────┬──────> typebox (;) └──> ( ──> threshold-clause ──> ) ──┘
reorganize clause
reorganize-clause = ───>
R
E
O
R
G
A
N
I
Z
E ──┬──>
A
R
E
A
S ──┬─> └──>
P
A
G
E
S ──┘
store lists clause
store-lists-clause = ──>
S
T
O
R
E
L
I
S
T
S ─────┐ ┌────────────────────┘ └┬>
I
N area-name ─┐ │ ┌─────────────┘ │ └─┬───────────────────────────────────────────────┬──┐ │ └─>
F
O
R ─> ( ─┬─> table-name ───────────┬─> ) ──┤ │ │ ├─> table-name. col-name ─┤ │ │ │ └──────── , <─────────────┘ │ │ └─────────────────────────<──────────────────────────┘ │ ┌───────────────────────────────────────────────────────┘ └>
I
N default-list-area ────────────────────>
Additional information available:
More InformationFOR table-namecolumn-name
More Information
Directs the database system to store the lists from one or more tables or columns of tables in a specified storage area. You must specify the default list storage area at the end of each store lists clause. SQL uses the default list storage area to store lists for which no storage area is specified. The default list storage area is RDB$SYSTEM unless you used the LIST STORAGE AREA clause of the CREATE DATABASE statement to specify a different default list storage area.
FOR table-name
Specifies the table or tables to which this storage map will apply. The named table must already be defined and cannot have a storage map associated with it. For each area, you can specify one FOR clause and list of table-names.
column-name
Specifies the column of a particular table which this storage map will apply. You must specify the table-name to which the column belongs, followed by a period, before the column name. The named column must already be defined and cannot have a storage map associated with it. For each area, you can specify one FOR clause and list of table-names and/or table-column names.
TABLE
CREATE TABLE─┐ ┌────────────┘ ├─> table-name ─┐ │ ┌───────────┘ │ └┬───────────────────────────────────┬┐ │ └─>
E
X
T
E
R
N
A
L
N
A
M
E typebox (I)typebox (S) external-name ─┘│ │ ┌─────────────────────────────────────┘ │ └─> ( ─┬┬─> col-definition ────┬┬─> ) ─┬─> typebox (;) │ │└─> table-constraint ──┘│ │ │ └───────────── , <───────┘ │ │ │ │ │ └─>
F
R
O
M ──> path-name ─┬────────────────┬─┘ └─> typebox (A)typebox (L)typebox (I)typebox (A)typebox (S) alias ─┘
Additional information available:
More InformationExamplestable nameschema nameexternal name
col definitionFROM clausetable constraint
More Information
The CREATE TABLE statement creates a table definition. A table
definition consists of a list of definitions of columns that make up
a row in the table. There are two ways to specify a table definition
in the CREATE TABLE statement:
o Directly, by naming the table, its columns and associated data
types, default values (optional), constraint definitions
(optional) and formatting clauses.
o Indirectly, by providing a path name for a data dictionary record
definition that specifies the table name, columns, and data
types.
The CREATE TABLE statement also creates a default access privilege
set for the table.
Examples
This example defines the PROGRAM_DATA table with a PRIMARY KEY:
SQL> CREATE TABLE PROGRAM_DATA
(
PROGRAM_NAME NAME_DOM
REFERENCES PROGRAMS (PROGRAM_NAME),
! Data_Name is a Data object for Program PROGRAM_NAME
DATA_NAME NAME_DOM
REFERENCES DATA_SPECS (DATA_NAME),
! Data_Linked_To is the Data object DATA_NAME is linked to. It can
! be blank.
DATA_LINKED_TO NAME_DOM,
! Repeat_Option is either None, Down, or Across
PROGRAM_DATA_REPEAT REPEAT_DOM
CHECK (PROGRAM_DATA_REPEAT IN ("N","D","A"))
CONSTRAINT PROGRAM_DATA_REPEAT_CHECK,
! Repeat_Count is >= 0
PROGRAM_DATA_REPEAT_COUNT COUNT_DOM
CHECK (PROGRAM_DATA_REPEAT_COUNT >= 0)
CONSTRAINT PROGRAM_DATA_COUNT_CHECK,
PRIMARY KEY (PROGRAM_NAME, DATA_NAME)
);
SQL>
table name
table-name =
─┬────────────────────────┬─> name-of-table ──┬─>
├─┬─> schema-name ─┬> . ─┘ │
│ └─> alias ───────┘ │
└───> typebox (") alias.name-of-table " ───────────────┘
schema name
schema-name = ─┬────────────────────────────────────────┬─┐ ├──────> catalog-name ────────────┬─> . ─┘ │ ├─> typebox (") ─> alias.catalog-name ─> typebox (") ─┘ │ │ ┌──────────────────────────────────────┘ │ └──────────────> name-of-schema ────────┬─> └─> typebox (") ─> alias.name-of-schema ─> typebox (") ─────────┘
external name
The name that you specify for a data definition when you create it is called the internal name. Each data definition also has an external name that it is known by to Rdb/VMS. Except in multischema databases, the internal name and external name for each element are the same. SQL requires that, for each definition of a particular type, the internal name must be unique within the schema and the external name must be unique within the database. If you create a table named EMPLOYEES in each schema of a multischema database, SQL generates a unique external name (for each EMPLOYEES table after the first one created) by adding a serial number and truncating the name, if necessary. If you prefer to specify an external name instead of relying on SQL to generate one, you can do so using the EXTERNAL NAME IS clause for any CREATE statement.
col definition
col-definition =
─> column-name ────┐
┌───────────────┘
├─┬─> data-type ───┬─┬──────────────────┬─┐
│ └─> domain-name ─┘ └─> default-value ─┘ │
│ ┌───────────────────────────────────────┘
│ └┬───────────────────┬─┬───────────────────────┬─┬─>
│ └┬> col-constraint ┬┘ └─> sql-and-dtr-clause ─┘ │
│ └────────<────────┘ │
└──>
C
O
M
P
U
T
E
D
B
Y value-expr ───────────────────────┘
Additional information available:
COLLATING_SEQUENCE_ISCOMPUTED_BYCHECKNOT_NULL
More Informationdata typedefault valuevalue exprcol constraintsql and dtr clause
More Information
The column definition clause specifies a data type or domain name and optional default value, column-constraints, SQL and DATATRIEVE formatting, and COMPUTED BY clauses for the column specified by the column name.
data type
data-type = ─┬─>
C
H
A
R ─┬────────┬───────────────────────────┬──> │ └─> (n) ─┘ │ ├─>
V
A
R
C
H
A
R (n) ───────────────────────────────┤ ├─>
L
O
N
G
V
A
R
C
H
A
R ──────────────────────────────┤ ├─>
T
I
N
Y
I
N
T ──────────────┬─────┬────────┬─────┤ ├─>
S
M
A
L
L
I
N
T ─────────────┤ └─> (n) ─┘ │ ├─>
I
N
T
E
G
E
R ──────────────┤ │ ├─>
B
I
G
I
N
T ───────────────┤ │ ├─>
Q
U
A
D
W
O
R
D ─────────────┤ │ ├─>
L
I
S
T
O
F
B
Y
T
E
V
A
R
Y
I
N
G ─┘ │ ├─>
D
E
C
I
M
A
L ─┬─┬─────────────────────────────┬─┤ ├─>
N
U
M
E
R
I
C ─┘ └─> ( ──> n ─┬────────┬─> ) ──┘ │ │ └─> , n ─┘ │ ├─>
F
L
O
A
T ─┬────────┬──────────────────────────┤ │ └─> (n) ─┘ │ ├─>
R
E
A
L ──────────────────────────────────────┤ ├─>
D
O
U
B
L
E
P
R
E
C
I
S
I
O
N ──────────────────────────┤ └─> date-time-data-types ──────────────────────┘
default value
default-value = DEFAULT ──┬──>literal ───────────┬─> ├──>
U
S
E
R ─────────────┤ ├──>
N
U
L
L ─────────────┤ ├──>
C
U
R
R
E
N
T
D
A
T
E ──────┤ ├──>
C
U
R
R
E
N
T
T
I
M
E ──────┤ └──>
C
U
R
R
E
N
T
T
I
M
E
S
T
A
M
P ─┘
Additional information available:
More InformationliteralMore Information
More Information
The default value of a column is the value stored in the database if an insert operation on a row specifies no value for that column. You can use literals, the NULL keyword, the current timestamp, or the user name as default values. If you do not specify a default value, SQL assigns NULL as the default value. If you specify a default value for a column in a table, it overrides any default value specified for the domain on which the column is based. You might have any of several possible reasons for specifying a default value for a column; for instance, you may want to store the most commonly used value, or you may want to store a value that highlights (is visual displays) the fact that no value was stored.
literal
A value expression that is either numeric, character string, or date.
USER
The user name of the process that invokes interactive SQL or runs a program.
NULL
A null value.
More Information
A value to be stored in a column if the row that is inserted does not include a value for that column. You can use literals, the NULL keyword, the current timestamp, or the user name as default values. If you do not specify a default value, a column inherits any default value from the domain. If you do not specify a default value for either the column or domain, SQL assigns NULL as the default value.
value expr
value-expr = ─┬─┬┬──────┬─┬───> char-value-expr ───┬─┬──────────┬> │ │├─> typebox (+) ─┤ ├───> datetime-function ─┤ ├─> typebox (+) ─┬─┐ │ │ │└─> - ─┘ ├───>
D
B
K
E
Y ─────────────┤ ├─> - ─┤ │ │ │ │ └───> (value-expr) ──────┘ ├─> typebox (*) ─┤ │ │ │ │ └─> typebox (/) ─┘ │ │ │ └───────────────────────────────────<─────────┘ │ └> char-value-expr ─┬> || ─> char-value-expr ──┬─┘ └─────────────<─────────────┘ A value expression is a symbol or string of symbols used to represent or calculate a single value. When you use a value expression in the COMPUTED BY clause of a column definition, SQL retrieves or calculates the value associated with the expression and uses that value when executing the statement. See the online HELP topic Value_expression More_Information for more information about value expressions.
Additional information available:
char value exprCAST functiondate-time functiondate-time examples
char value expr
char-value-expr = ───┬─>
C
A
S
T ──> ( ─> cast-operand AS data-type ─> ) ─┬─> ├─> column-name ──────────────────────────────────┤ ├─> literal ──────────────────────────────────────┤ ├─>
U
S
E
R ─────────────────────────────────────────┤ ├─> parameter ────────────────────────────────────┤ ├─> (col-select-expr) ────────────────────────────┤ └─>
S
U
B
S
T
R
I
N
G (char-value-expr FROM ──┐ │ ┌───────────────────────────────────┘ │ └> start-position ┬────────────────────┬ ) ─────┘ └>
F
O
R string-length ┘ A character value expression represents a value that belongs to the CHAR, VARCHAR, or LONG VARCHAR data type. You can link two character value expressions together using the concatenation operator.
CAST function
cast-function =
─>
C
A
S
T ─> ( ─> cast-operand AS ─┬─> data-type ───┬─> ) ─>
└─> domain-name ─┘
The CAST function converts a column of one data type to another
datatype. The data type and cast operand that you specify can be any
data type except LIST OF BYTE VARYING (SEGMENTED STRING).
If you specify the name of a domain in the AS clause, Rdb/VMS uses
the current data type of that domain as the output data type of the
CAST function.
If you use an INTERVAL as the data type, then the interval qualifier
must specify only one field.
Below is an example of an SQL statement that converts the average of
the SALARY_AMOUNT column from scientific notation to an integer with
two decimal places:
SQL> SELECT AVG(SALARY_AMOUNT),
cont> CAST(AVG(SALARY_AMOUNT) AS INTEGER(2))
cont> FROM SALARY_HISTORY;
2.652896707818930E+004 26528.97
For additional examples, type HELP FUNCTIONS CAST_FUNCTION.
date-time function
date-time-function = ┬>
C
U
R
R
E
N
T
D
A
T
E ───────────────────────────────────┬─> ├>
C
U
R
R
E
N
T
T
I
M
E ──────┬────────────────────┬───────┤ │ └─> time-precision ──┘ │ ├>
C
U
R
R
E
N
T
T
I
M
E
S
T
A
M
P ─┬────────────────────────┬───┤ │ └─> timestamp-precision ─┘ │ └>
E
X
T
R
A
C
T ─────────┐ │ ┌─────────────────┘ │ └─>( ─> date-time-field FROM extract-source ─> ) ┘ The EXTRACT, CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP keywords are accessible from anywhere that an expression is allowed in Rdb/VMS. You can also use these keywords within triggers.
date-time examples
The following example inserts the date into the START_DATE field of
an employees record using the CURRENT_TIMESTAMP function:
SQL> INSERT INTO EMPLOYEES(JOB_START ...)
VALUES (CURRENT_TIMESTAMP, ...);
If you use the CURRENT_TIMESTAMP keyword more than once within a
statement, it retains the same value for the date and time.
The following example finds the longest serving employee still with
the company:
SELECT E.LAST_NAME,CURRENT_DATE,JH.JOB_START,
EXTRACT(MONTH FROM (CURRENT_DATE - CAST(JH.JOB_START AS DATE
ANSI))
MONTH(9))
FROM EMPLOYEES E, JOB_HISTORY JH
WHERE E.EMPLOYEE_ID = JH.EMPLOYEE_ID AND
(CURRENT_DATE - CAST(JH.JOB_START AS DATE ANSI)) MONTH (9) =
(SELECT MAX((CURRENT_DATE - CAST(JH.JOB_START AS DATE ANSI))
MONTH (9))
FROM JOB_HISTORY JH WHERE JH.JOB_END IS NULL);
E.LAST_NAME JH.JOB_START
Nash 1991-04-12 1979-02-23 146
Gray 1991-04-12 1979-02-10 146
Myotte 1991-04-12 1979-02-04 146
Kinmonth 1991-04-12 1979-02-12 146
Lapointe 1991-04-12 1979-02-20 146
5 rows selected
col constraint
col-constraint = ─┬─>
P
R
I
M
A
R
Y
K
E
Y ─────────────────┬──┐ ├─>
N
O
T
N
U
L
L ────────────────────┤ │ ├─>
U
N
I
Q
U
E ──────────────────────┤ │ ├─>
C
H
E
C
K (predicate) ───────────┤ │ └─> references-clause ───────────┘ │ ┌───────────────────────<───────────┘ └─┬─────────────────>──────────────┬─> └─>
C
O
N
S
T
R
A
I
N
T constraint-name ─┘
Additional information available:
More Informationreferences clause
More Information
A col-constraint is a constraint definition that applies to the
specified column. The five types of column constraints are PRIMARY
KEY, NOT NULL, UNIQUE, CHECK, and foreign key constraints. Foreign
key constraints are created with the REFERENCES clause. You can also
optionally specify a name for a constraint definition.
The PRIMARY KEY column constraint declares a column to be a primary
key. SQL requires that values in this column be unique and not null.
Therefore, you need not specify the UNIQUE and NOT NULL column
constraints for a primary key column.
The NOT NULL column constraint restricts values in the column to
non-null values.
The UNIQUE column constraint specifies that values in the column
named must be unique. You can use either the UNIQUE or PRIMARY KEY
keywords to define a column as a unique key for a table.
The CHECK column constraint specifies a predicate that column values
inserted into the table must satisfy. Predicates in CHECK table
constraints can refer directly only to the column with which they are
associated.
The optional CONSTRAINT constraint-name specifies a name for a column
constraint that is used in several ways:
o The INTEG_FAIL error message specifies the name when an INSERT,
UDPATE, or DELETE statement violates the constraint
o ALTER TABLE DROP CONSTRAINT statements specify the name to drop a
table constraint
o SHOW TABLE statements display the names of column and table
constraints
The name of the constraint is used in error messages and ALTER TABLE
DROP CONSTRAINT, SHOW TABLE, DECLARE TRANSACTION and SET TRANSACTION
statements. The CONSTRAINT clause is optional. If you omit the
constraint name, SQL creates a name. However, DIGITAL recommends
that you always name column and table constraints. The constraint
names generated by SQL may be obscure and, in programs, may change
between compilation and running.
references clause
references-clause =
REFERENCES referenced-table-name ───┐
┌─────────────────<────────────────┘
└─┬─────────────────────>───────────────────┬─>
└─> ( ─┬─> referenced-column-name ─┬─> ) ─┘
└─────────── , <────────────┘
Specifies the name of a column or columns that are a primary key or
unique key in the referenced table. When the REFERENCES clause is
selected as a column-constraint, the column specified in the
col-definition clause becomes a foreign key for the referencing table
(the table being defined). When the REFERENCES clause is selected as
a table constraint, the column name or column names specified in the
FOREIGN KEY clause become a foreign key for the referencing table.
The referenced_table_name is the name of the table that contains the
unique key or primary key referenced by the referencing table. You
must have the SQL access right REFERENCES or CREATETAB to the
referenced table.
For a column constraint, the referenced_column_name is the name of
the column that is a unique key or primary key in the referenced
table. For a table constraint, the referenced_column_name is the
name of the column or columns that are a unique key or primary key in
the referenced table. If you do not supply a referenced_column_name,
the primary key is selected by default.
sql and dtr clause
sql-and-dtr-clause = ─┬─>
Q
U
E
R
Y
H
E
A
D
E
R typebox (I)typebox (S) ─┬> quoted-string ──┬─────────────────┬─> │ └────── typebox (/) <────────┘ │ ├─>
E
D
I
T
S
T
R
I
N
G typebox (I)typebox (S) quoted-string ─────────────────────────┤ │ │ ├─>
Q
U
E
R
Y
N
A
M
E Ftypebox (O)typebox (R) ─┬─> typebox (D)typebox (T)typebox (R) ────────┬─> typebox (I)typebox (S) quoted-string ─┤ │ └─> typebox (D)typebox (A)typebox (T)typebox (A)typebox (T)typebox (R)typebox (I)typebox (E)typebox (V)typebox (E) ─┘ │ └─>
D
E
F
A
U
L
T
V
A
L
U
E Ftypebox (O)typebox (R) ─┬─> typebox (D)typebox (T)typebox (R) ────────┬─> typebox (I)typebox (S) literal ────┘ └─> typebox (D)typebox (A)typebox (T)typebox (A)typebox (T)typebox (R)typebox (I)typebox (E)typebox (V)typebox (E) ─┘ Optional formatting clauses allow you to modify data displays or query characteristics for interactive SQL users and DATATRIEVE users. A query header specifies a string that interactive SQL or DATATRIEVE displays in place of the column name when it retrieves values from a column. An edit string specifies a string that controls how interactive SQL or DATATRIEVE formats the display of values in a column. DEFAULT VALUE and QUERY NAME are active only from DATATRIEVE.
COLLATING_SEQUENCE_IS
The COLLATING SEQUENCE IS clause specifies a collating sequence. A collating sequence is the sequence in which characters are ordered for sorting, merging, and comparing. The VMS National Character Set (NCS) provides a set of predefined collating sequences and lets you define collating sequences of your own.
COMPUTED_BY
The COMPUTED BY clause specifies that the value of this column is calculated from values in other columns and constant expressions. You can use the COMPUTED BY clause in a CREATE TABLE or an ALTER TABLE statement. In a COMPUTED BY clause, the column name that you supply in your column definition must be different from the name of any other existing column in the table. Any column that you refer to in the definition of a computed column cannot be deleted from that table unless you first delete the computed column. SQL does not allow UNIQUE or PRIMARY KEY constraints, REFERENCE clauses, default values, or default values for DATATRIEVE in computed columns.
CHECK
The CHECK column constraint specifies a predicate that column values inserted into the table must satisfy. Predicates in CHECK table constraints can refer directly only to the column with which they are associated.
NOT_NULL
The NOT NULL column constraint restricts values in the column to non-null values.
FROM clause
The FROM clause specifies the data dictionary path name of a dictionary record definition. SQL creates the table and all associated columns using the definition from this field. You can specify either a full data dictionary path name or a relative data dictionary path name. You cannot specify support clauses for SQL or DATATRIEVE or constraints if you use the FROM path-name form of the CREATE TABLE statement.
Additional information available:
alias
A name for an attachment to a particular database. SQL adds the table definition to the database referred to by the alias. If you do not specify an alias, SQL adds the table definition to the default database.
table constraint
table-constraint = ─┬─>
P
R
I
M
A
R
Y
K
E
Y ─> ( ─┬─> column-name ─┬─> ) ──────┬──┐ │ └─────── , <─────┘ │ │ ├─>
U
N
I
Q
U
E ─> ( ─┬─> column-name ─┬─> ) ───────────┤ │ │ └─────── , <─────┘ │ │ ├─>
C
H
E
C
K (predicate) ─────────────────────────────┤ │ └─>
F
O
R
E
I
G
N
K
E
Y ─> ( ─┬─> column-name ─┬─> ) ──┐ │ │ └─────── , <─────┘ │ │ │ ┌───────────────────────<────────────────────┘ │ │ └─> references-clause ─────────────>─────────────┘ │ ┌──────────────────<────────────────────────────────┘ └─┬───────────────────────────────┬─> └─>
C
O
N
S
T
R
A
I
N
T constraint-name ─┘
Additional information available:
More Informationreferences clause
More Information
A table-constraint is a constraint definition that applies to the
whole table. The four types of table constraints are PRIMARY KEY,
UNIQUE, CHECK, and FOREIGN KEY constraints. You can also optionally
specify a constraint name. A column must be defined in a table
before you can specify the column in a table constraint definition.
The PRIMARY KEY constraint is used to declare a column or columns as
a primary key for the table being defined. Any foreign key that
refers to this column must refer to this primary key.
The UNIQUE table constraint specifies that the combination of values
for the columns named must be unique in a row. You can use either
the UNIQUE or PRIMARY KEY keywords to define one or more columns as a
unique key for a table.
The CHECK table constraint specifies a predicate that column values
inserted into the table must satisfy. Predicates in CHECK table
constraints can refer to any column in the table. Column select
expressions within the predicate can refer to other tables in the
database.
The FOREIGN KEY table constraint specifies the column or columns that
you want to declare as a foreign key in the table you are defining.
The optional CONSTRAINT constraint-name clause specifies a name for a
column or table constraint. The name specified is used in several
ways:
o The INTEG_FAIL error message specifies the name when an INSERT,
UDPATE, or DELETE statement violates the constraint
o ALTER TABLE DROP CONSTRAINT statements specify the name to drop a
table constraint
o SHOW TABLE statements display the names of column and table
constraints
references clause
references-clause =
REFERENCES referenced-table-name ───┐
┌─────────────────<────────────────┘
└─┬─────────────────────>───────────────────┬─>
└─> ( ─┬─> referenced-column-name ─┬─> ) ─┘
└─────────── , <────────────┘
Specifies the name of a column or columns that are a primary key or
unique key in the referenced table. When the REFERENCES clause is
selected as a column-constraint, the column specified in the
col-definition clause becomes a foreign key for the referencing table
(the table being defined). When the REFERENCES clause is selected as
a table constraint, the column name or column names specified in the
FOREIGN KEY clause become a foreign key for the referencing table.
The referenced_table_name is the name of the table that contains the
unique key or primary key referenced by the referencing table. You
must have the SQL access right REFERENCES or CREATETAB to the
referenced table.
For a column constraint, the referenced_column_name is the name of
the column that is a unique key or primary key in the referenced
table. For a table constraint, the referenced_column_name is the
name of the column or columns that are a unique key or primary key in
the referenced table. If you do not supply a referenced_column_name,
the primary key in the referenced table is used by default.
TRIGGER
CREATE TRIGGER ──> trigger-name ────────┐ ┌───────────────────────────────────────┘ └─┬───────────────────────────────────┬─┐ └─>
E
X
T
E
R
N
A
L
N
A
M
E typebox (I)typebox (S) external-name ─┘ │ ┌────────────────<──────────────────────┘ └┬─>
B
E
F
O
R
E ─┬─┬─>
I
N
S
E
R
T ────────────────────────────────┬─┐ └─>
A
F
T
E
R ──┘ ├─>
D
E
L
E
T
E ────────────────────────────────┤ │ └─>
U
P
D
A
T
E ──┬──────────────────────────┬──┘ │ └──>
O
F ─┬─> column-name ─┬┘ │ └─── , <─────────┘ │ ┌────────────────────────────────<──────────────────────────┘ └─>
O
N table-name ─┬────────────>──────────┬┬─> triggered-action ─┬>typebox (;) └─> referencing-clause ─┘└───────────<─────────┘
Additional information available:
More Informationtrigger namecolumn nametable namereferencing clause
triggered action
More Information
The CREATE TRIGGER statement creates a trigger for the specified table. A trigger defines the actions to occur before or after an update of the table in order to maintain the referential integrity of the database. A trigger can be thought of as a constraint on a single table, which takes effect at a specific time for a particular type of update and causes a series of triggered actions to be performed. With triggers, you can define the rules and actions necessary to manage updates and deletions of rows containing unique keys or primary keys that are referred to by foreign keys. Creating a trigger requires SELECT and CREATETAB access to the subject table, and if any triggered statement specifies some form of update operation, requires SELECT, DBCTRL, and the appropriate type of update (DELETE, UPDATE, INSERT) access to the tables specified by the triggered action statement. You must execute this statement in a read/write transation. If you issue this statement when there is no active transaction, SQL starts a read/write transaction implicitly. Other users are allowed to be attached to the database when you issue the CREATE TRIGGER statement.
trigger name
The name of the trigger being defined. The name must be unique within the database.
column name
The name of the column within the specified table to be checked for deletion, modification, or insertion. This argument is only used with UPDATE triggers.
table name
The name of the table for which this trigger is defined.
referencing clause
referencing-clause = REFERENCING ─┬┬─>
O
L
D typebox (A)typebox (S) ──> old-correlation-name ─┬┬─> │└─>
N
E
W typebox (A)typebox (S) ──> new-correlation-name ─┘│ └──────────────<───────────────────────┘
Additional information available:
More Information
The REFERENCING clause permits you to specify whether you want to reference the row values as they existed before an UPDATE operation occurred or the new row values after they have been applied by the UPDATE operation. Do not use this clause with INSERT or DELETE operations. You can specify each option (OLD AS old-alias and NEW AS new-alias) only once in the REFERENCING clause.
Additional information available:
old alias
A temporary name used to refer to the row values as they existed before an UPDATE operation occurred.
new alias
A temporary name used to refer to the new row values to be applied by the UPDATE operation.
triggered action
triggered-action =
───┬─────────>────────┬─> ( ─┬> triggered-statement ─┬─> ) ─┐
└>
W
H
E
N predicate ─┘ └─────────── ,<─────────┘ │
┌─────────────────────────────<──────────────────────────┘
└─┬────────>────────┬─>
└─>
F
O
R
E
A
C
H
R
O
W ─┘
Additional information available:
triggered statementMore Information
triggered statement
triggered-statement =
──┬─> delete-statement ─┬──>
├─> update-statement ─┤
├─> insert-statement ─┤
└─>
E
R
R
O
R ────────────┘
A statement that updates the database or generates an error message.
Additional information available:
delete statementupdate statementinsert statement
delete statement
A DELETE statement that specifies the row of a table or view that you want to delete. You will receive an error message if you specify CURRENT OF cursor-name with the DELETE statement's WHERE clause.
update statement
An UPDATE statement that specifies the row of a table or view that you want to modify. You will receive an error message if you specify CURRENT OF cursor-name with the UPDATE statement's WHERE clause.
insert statement
An INSERT statement that specifies the new row or rows you want to add to a table or view.
ERROR
A triggered ERROR statement provides the following message: RDMS-E-TRIG_ERROR, Trigger 'trigger_name' forced an ERROR.
More Information
Consists of an optional predicate and some number of triggered statements. If specified, the predicate must evaluate to true in order for the triggered statements in the trigger action clause to execute. Each triggered statement is executed in the order in which it appears within the triggered action definition. The FOR EACH ROW clause determines whether the triggered action is evaluated once per triggering statement, or for each row of the subject table that is affected by the triggering statement. If the FOR EACH ROW clause is not specified, the triggered action is evaluated only once, and row values are not available to the triggered action.
VIEW
CREATE VIEW ──> view-name ───────────────────────────┐ ┌──────────────────────────────────────────────────┘ └┬───────────────────────────────────┬─────────────┐ └─>
E
X
T
E
R
N
A
L
N
A
M
E typebox (I)typebox (S) external-name ─┘ │ │ ┌────────────────────────────────────────────────────┘ └┬────────────────────────>────────────────────────┬─┐ └─> ( ─┬─> column-name ───┬──────────────┬─┬─> ) ─┘ │ │ ┌────────────────┘ │ │ │ │ └┬─┬────────────────────────┬─┬─┘ │ │ │ │ └─> sql-and-dtr-clause ──┘ │ │ │ │ └─────────────<──────────────┘ │ │ └───────────────── , <──────────────┘ │ │ ┌────────────────────────────────────────────────────┘ └─>
A
S select-expr ───┬────────────────────────┬──> typebox (;) └─> check-option-clause ─┘
Additional information available:
More Informationview nameschema nameselect exprsql and dtr clause
order by clauselimit to clausecheck option clause
More Information
The CREATE VIEW statement defines a view. A view is a table whose data is not physically stored. Rather, a view is a virtual structure that refers to rows stored in other tables. When the CREATE VIEW statement executes, it adds a view definition to the database and, if the schema was declared by path name, to the data dictionary. The CREATE VIEW statement also creates a default access privilege set for the view. Select More_Information to continue.
Additional information available:
More Information
Do not refer to read-only views in INSERT, UPDATE, or DELETE
statements. SQL considers as read-only views those with select
expressions that:
o Use the DISTINCT argument to eliminate duplicate rows from the
result table
o Name more than one table or view in the FROM clause
o Specify a subquery in the predicate of the WHERE clause
o Include a function in the select list
o Contain a GROUP BY or HAVING clause
view name
view-name =
─┬────────────────────────┬─> name-of-view ──┬─>
├─┬─> schema-name ─┬> . ─┘ │
│ └─> alias ───────┘ │
└───> typebox (") alias.name-of-view " ───────────────┘
schema name
schema-name = ─┬────────────────────────────────────────┬─┐ ├──────> catalog-name ────────────┬─> . ─┘ │ ├─> typebox (") ─> alias.catalog-name ─> typebox (") ─┘ │ │ ┌──────────────────────────────────────┘ │ └──────────────> name-of-schema ────────┬─> └─> typebox (") ─> alias.name-of-schema ─> typebox (") ─────────┘
select expr
select-expr = ─┬─┬─> select-clause ─────┬─┬────────────────────┬─┐ │ └─>( select-expr ) ────┤ └─> order-by-clause ─┘ │ │ ┌─────────<────────────┘ │ │ └─>
U
N
I
O
N ─┬───────┬────┐ │ │ └─>
A
L
L ─┘ │ │ └───────<────────────────┘ │ ┌─────────────────────────────────────────────────┘ └─┬────────────────────┬───────────────────────────> └─> limit-to-clause ─┘
Additional information available:
select clause
select-clause = SELECT ─┬──────>──────┬─> select-list ──┐ ├─>
A
L
L ──────┤ │ └─>
D
I
S
T
I
N
C
T ─┘ │ ┌──────────────────────────────<────────┘ └─>
F
R
O
M ─┬┬─> table-name ─┬┬─────>─────────────┬┬─┐ │└─> view-name ──┘└> correlation-name ┘│ │ └────────── , <────────────────────────┘ │ ┌──────────────────────────────────────────────────┘ └┬─────────>────────┬┬─────────────>──────────────┬┐ └>
W
H
E
R
E predicate ┘└>
G
R
O
U
P
B
Y ─┬> column-name ┬┘│ └─────── , <───┘ │ ┌────────────────────────────────<─────────────────┘ └┬──────────>──────────┬──┬────────────────────┬─┐ └─>
H
A
V
I
N
G predicate ─┘ └─> limit-to-clause ─┘ │ ┌────────────────────────────────────────────────┘ └┬───────────────────┬──────────────────────────────> └─> order-by-clause ┘
UNION
The union operator merges the results of a select expression or select clause with another select expression or select clause into one result table by appending the values of columns in one table with the values of columns in other tables.
Additional information available:
ALL
The ALL qualifier specifies that duplicate rows should not be eliminated from the result table.
More Information
A select expression is an expression that defines which columns and rows of which tables SQL includes in the view. The select expression can name only tables in the same database as the view.
sql and dtr clause
sql-and-dtr-clause = ─┬─>
Q
U
E
R
Y
H
E
A
D
E
R typebox (I)typebox (S) ─┬> quoted-string ──┬─────────────────┬─> │ └────── typebox (/) <────────┘ │ ├─>
E
D
I
T
S
T
R
I
N
G typebox (I)typebox (S) quoted-string ─────────────────────────┤ │ │ ├─>
Q
U
E
R
Y
N
A
M
E Ftypebox (O)typebox (R) ─┬─> typebox (D)typebox (T)typebox (R) ────────┬─> typebox (I)typebox (S) quoted-string ─┤ │ └─> typebox (D)typebox (A)typebox (T)typebox (A)typebox (T)typebox (R)typebox (I)typebox (E)typebox (V)typebox (E) ─┘ │ └─>
D
E
F
A
U
L
T
V
A
L
U
E Ftypebox (O)typebox (R) ─┬─> typebox (D)typebox (T)typebox (R) ────────┬─> typebox (I)typebox (S) literal ────┘ └─> typebox (D)typebox (A)typebox (T)typebox (A)typebox (T)typebox (R)typebox (I)typebox (E)typebox (V)typebox (E) ─┘ Optional formatting clauses allow you to modify data displays or query characteristics for interactive SQL users and DATATRIEVE users. A query header specifies a string that interactive SQL or DATATRIEVE displays in place of the column name when it retrieves values from a column. An edit string specifies a string that controls how interactive SQL or DATATRIEVE formats the display of values in a column. DEFAULT VALUE and QUERY NAME are active only from DATATRIEVE.
order by clause
order-by-clause = ──>
O
R
D
E
R
B
Y ─┬┬─> column-name ─┬─┬────>────┬─┬──> │└─> integer ─────┘ ├─>
A
S
C ──┤ │ │ └─>
D
E
S
C ─┘ │ └──────────── , <───────────────┘
limit to clause
limit-to-clause = ───>
L
I
M
I
T
T
O ────> row-limit ───>
R
O
W
S ──>
check option clause
check-option-clause = WITH CHECK OPTION ──┬──────────────>─────────────────┬───> └─>
C
O
N
S
T
R
A
I
N
T check-option-name ─┘ The check option clause places restrictions on updates made to a view. This clause ensures that any rows that are inserted or updated in a view conform to the definition of the view. Do not specify the check option clause with views that are read-only.