CREATE TABLE

The CREATE TABLE statement defines a table. You provide the name of the table and the names and attributes of its columns. You can grant or revoke authorization for other people to use a table you created. See GRANT and REVOKE.

The syntax of the CREATE TABLE statement is:

CREATE TABLE tablename (column1 type1 NOT NULL,
column2 type2 . . .)
  IN space-name

tablename

The name you assign to the table.

If your installation uses DBCS data, names of tables cannot contain double-byte characters that are internally represented as double quotation marks unless your database specifically supports double-byte characters in table names. (See Names with double-byte characters.)

column1 type1

The name you assign to the first column, and the data type describing it.

If the data type is CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, or DECIMAL, you must specify the maximum length of a data element, in parentheses. For DECIMAL, you must also specify the number of places after the assumed decimal point.

column2 type2

The name you assign to the second column and the data type describing it.

NOT NULL

Optional for any column you define. If you use NOT NULL in the table definition, then any attempt to have no value in the corresponding column of the table produces an error message. Omitting NOT NULL allows null values in the column.

IN space-name

Refers to a table space or a dbspace in which the table is to be created. This clause is needed only if your installation does not provide a space to be used by default.

You can find the space-name used when QMF creates tables for SAVE DATA or IMPORT TABLE by issuing the QMF command DISPLAY PROFILE. See the appropriate Installing and Managing QMF manual for instructions on how to find and provide these names to users.

The following CREATE statement defines a table called PERS. The columns in PERS have the same characteristics as Q.STAFF, but contain no data.

CREATE TABLE PERS
(ID SMALLINT NOT NULL,
NAME VARCHAR(9),
DEPT SMALLINT,
JOB CHAR(5),
YEARS SMALLINT,
SALARY DECIMAL(7,2),
COMM DECIMAL(7,2))
IN space-name

ID

The employee number is a small integer and null cannot be specified for it.

NAME

The maximum length of the name is nine characters.

DEPT

The department number is small integer.

JOB

The name of the job has five characters.

YEARS

The number of years is small integer.

SALARY

A seven digit number with two decimal positions.

COMM

A seven digit number with two decimal positions. (Remember the final parenthesis.)

You can use NOT NULL with any set of columns in the CREATE TABLE statement; in the example, it appears with column ID. It means that any row entered into PERS must have, at the very least, an employee number.

This statement defines the Q.APPLICANT table:

CREATE TABLE APPLICANT
(TEMPID  SMALLINT  NOT NULL,
NAME     VARCHAR(9),
ADDRESS  VARCHAR(17),
EDLEVEL  SMALLINT,
COMMENTS VARCHAR(29))
IN space-name

This statement defines the Q.INTERVIEW table:

CREATE TABLE INTERVIEW
(TEMPID   SMALLINT,
INTDATE   DATE,
STARTTIME TIME,
ENDTIME   TIME,
MANAGER   SMALLINT,
DISP      VARCHAR(6),
LASTNAME  VARCHAR(9),
FIRSTNAME VARCHAR(9))
IN space-name

Defining the table does not put data into it. For ways of entering data into it, see INSERT INTO.