CREATE TABLE Statement

statement_CREATE_TABLE ::= CREATE [ IF NOT EXISTS ] TABLE [scheme.]table_name [ table_flag ]
     	(element_description {, element_description} ... )
table_flag ::= REC_PRIVILS 
element_description ::= column_description | constraint_description
constraint_description ::= [CONSTRAINT constraint_name] { index_description | internal_integrity_constraint
	| referential_integrity_constraint }

The CREATE TABLE statement creates a new table. It contains the description of the individual table columns and the constraint description. Constraints may be indexes, internal integrity constraints and referential integrity constraints.

If the schema (application) is not specified, then:

If the CREATE TABLE statement is called from a stored procedure, the table is created in the same schema the procedure belongs to.
Otherwise, the table is created in the active schema.

The maximum table name length is 31 characters. Tables must have a unique name in the specified schema. If a table of the specified name already exists, the statement does not execute if the IF NOT EXISTS clause is used, otherwise the error sqlstate 40004 (KEY_DUPLICITY) occurs. The table name may not contain dots or grave accents (`).

Constraints may or may not be given names. If a constraint name is not specified in the CREATE TABLE statement, the server will assign a default value. All constraint names must be unique.

Flags are table properties specific to 602SQL Server. Only a single flag currently exists (REC_PRIVILS). This flag states whether the privileges for individual records are enabled.

Example:

CREATE TABLE `employee` REC_PRIVILS (
  number INTEGER DEFAULT employee_sequence.NEXTVAL PRIMARY KEY NOT NULL,
  name CHAR(12) COLLATE CSISTRING, 
  surname CHAR(20) COLLATE CSISTRING NOT NULL INDEX,
  date DATE,
  salary NUMERIC(14,2) DEFAULT 10000 INDEX CHECK (plat > 0),
  department SMALLINT,
  picture BLOB,
  CONSTRAINT dep INDEX (department),
  CONSTRAINT full_name INDEX (surname, name) NOT NULL,
  CONSTRAINT insurance INDEX (salary*0.135 DESC),
  CONSTRAINT department_employee FOREIGN KEY (department) REFERENCES department (department_number) ON UPDATE CASCADE 
    INITIALLY DEFERRED)

The demonstration table called EMPLOYEE (grave accents would be necessary if the table name contained blank spaces) is defined with record privileges. Integer NUMBER is the primary key that is assigned the value automatically by a sequence. This column may not be NULL. Two czech strings are used for NAME, SURNAME is a non-unique index as well as the pair SURNAME and NAME. Salary is stored in a column of NUMERIC(14,2) type, with the default value 10000 and with check SALARY>0 and a non-unique index The DEPARTEMENT column is interesting because it is assigned values from a counter from a table called DEPARTMENT (see referential integrity). If the DEPARTMENT_NUMBER in the counter is modified, it is modified in this table as well using the ON UPDATE CASCADE.

Database Tables

Table Column Description