Examples of database and table creation

The following examples use the create keyword to create the sample staff database and define the tables.

These example OQL statements illustrate the use of the column constraints and the default keyword.

Example 1

create database staff;          // creates the staff database

The following insert defines the managers table.

create table staff.managers
(
        EmployeeID                       int   NOT NULL   PRIMARY KEY,
        Name                             text  NOT NULL,
        Department                       text  default "Sales", 
        Gender                           text,
        Age                              int,
        unique ( EmployeeID )         // indicates that the data in the 
                                      // EmployeeID column must be unique.
);

For the managers table:

  • The EmployeeID and Name columns cannot be NULL.
  • The EmployeeID column is the primary key and must be unique.
  • If no value is inserted into the Department column for a given record it takes the value "Sales".

Example 2

The following insert creates the staff.employees table. 

create table staff.employees
(
        EmployeeID              int             NOT NULL   PRIMARY KEY,
        Name                    text            NOT NULL,
        Skills                  list type text,
        Gender                  text,
        Age                     int   // There is no comma here because this
                                      // is the last entry.
);

For the staff.employees table:

  • The EmployeeID and Name columns cannot be NULL.
  • The Skills column is a list of text strings.

Example 3

The following insert creates the staff.contractors table.

create table staff.contractors
(
        EmployeeID             int NOT NULL PRIMARY KEY,
        Name                   text NOT NULL,
        Gender                 text,
        Age                    int,
        ExtraInfo              object type vblist,
        volatile
);

For the staff.contractors table:

  • The ExtraInfo column contains a list of varbinds.