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.

Syntax

The syntax of the CREATE TABLE statement is:

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

tablename: The name that you assign to the table.
If your site 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.
column1 type1: The name that 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 that 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 site does not provide a space to be used by default.
To find the space name that is used when QMF™ creates tables for SAVE DATA or IMPORT TABLE commands, issue the QMF command DISPLAY PROFILE and look at the value of the SPACE option.

Examples

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 data type of the department number column 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 the ID column. 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.