Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Creating Tables


After the physical file space has been allocated on a disk for the databank, (CREATE DATABANK), you can create the tables. The basic CREATE TABLE statement defines the columns in the table, the primary key, any unique or foreign keys and which databank the table is to be stored in. Table names and column names may be up to 128 characters long.

As a convention, we have defined primary key column(s) as the first column(s) in the example definitions. However, this is not a necessity; primary key columns may be defined anywhere in the column list. Primary keys are always NOT NULL, so there is no need to explicitly state that in the table definition (they are included in the examples here for clarity).

Create Table Statement

The statement for creating a table has the general form:

 CREATE TABLE table-name;

Example

Create the table EXCHANGE_RATE with two columns.

The table shall be as follows:

 CREATE TABLE EXCHANGE_RATE (CURRENCY CHAR(3) NOT NULL,
                             RATE     DECIMAL(6,3),
                             PRIMARY KEY (CURRENCY))
        IN HOTELDB;
 

The CREATE TABLE clause defines the name of the table followed by a column list, which includes the names of the columns in the table, their data type, if they should allow the NULL indicator and the primary key declaration. Each item in the column-list is separated from the next by a comma, and the entire list is enclosed in parentheses.

A table definition may only include one primary key clause. The primary key can be made up of more than one column.

The IN clause states which databank the table is to be stored in. This clause may be omitted; if the IN clause is not specified, Mimer SQL will select the 'best' databank in which to place the table, see the Mimer SQL Reference Manual for details of how the best databank is chosen.

The empty table now exists in the databank. Data is inserted into the table with the INSERT statement, see Inserting Data.

The preceding example shows the simplest form of column list. The following variants may also be used:

The BOOK_GUEST Table

The BOOK_GUEST table in the example database is defined with many of the options that can be used in creating tables. See the Mimer SQL Reference Manual for a full description of the table creation facilities.

The BOOK_GUEST table is defined as follows:

 CREATE TABLE BOOK_GUEST (RESERVATION     INTEGER(5)          NOT NULL,
                       BOOKING_DATE DATE DEFAULT CURRENT_DATE NOT NULL,
                          HOTELCODE       HOTELCODE           NOT NULL,
                          ROOMTYPE        ROOMTYPE            NOT NULL,
                          COMPANY         VARCHAR(100)        NOT NULL,
                          TELEPHONE       CHAR(15),
                          RESERVED_FNAME  PERSONNAME,
                          RESERVED_LNAME  PERSONNAME,
                          ARRIVE          DATE                NOT NULL,
                          DEPART          DATE                NOT NULL,
                          GUEST_FNAME     PERSONNAME,
                          GUEST_LNAME     PERSONNAME,
                          ADDRESS         VARCHAR(50),
                          CHECKIN         DATE,
                          CHECKOUT        DATE,
                          ROOMNO          ROOMNO,
                          PAYMENT         CHAR(10),
        PRIMARY KEY (RESERVATION),
        FOREIGN KEY (HOTELCODE) REFERENCES HOTEL,
        FOREIGN KEY (ROOMTYPE)  REFERENCES ROOMTYPES,
        FOREIGN KEY (ROOMNO)    REFERENCES ROOMS ON DELETE NO ACTION,
        CHECK (ARRIVE < DEPART AND CHECKIN <= CHECKOUT))
        IN HOTELDB;               
 

The ordering of column specifications, key clauses and check conditions is not fixed. If desired, the key and check clauses can be written in association with the respective column specifications:

 CREATE TABLE BOOK_GUEST 
              (RESERVATION  INTEGER(5) NOT NULL PRIMARY KEY,
               BOOKING_DATE DATE       DEFAULT CURRENT_DATE NOT NULL,
               HOTELCODE    HOTELCODE  NOT NULL REFERENCES HOTEL,
               ROOMTYPE     ROOMTYPE   NOT NULL REFERENCES ROOMTYPES,
               ...

Column Definitions

Columns should in general be defined as NOT NULL unless there is a specific reason for using the NULL value in the column (e.g. CHECKIN and CHECKOUT in the table BOOK_GUEST, where NULL indicates that the reservation has not checked in or out). The presence of NULL values can often complicate the formulation of queries, see Handling NULL Values.

Note: Take particular care to exclude NULL from numerical columns which are to be used for mathematical operations.

In the example database, domains are used for many columns to help in maintaining database integrity. By using the same domain for columns in different tables, the column data types are guaranteed to be consistent. See Creating Domains for more information

The Primary Key Constraint

The purpose of a primary key is to define a key value that uniquely identifies each table row, therefore the primary key value for each row in the table must be unique.

The primary key constraint can consist of more than one column in the table. The choice of columns to use as the primary key is determined by the relational model for the database, which is outside the scope of this manual.

Unique Constraints

A unique constraint can defined for one or more columns in the table. The list of columns that make up the unique constraint are specified in the UNIQUE clause for the table when it is created.

This is the recommended way of defining a unique constraint, the other methods described below are mentioned for information only.

Specifying UNIQUE in the definition of a column in the table is equivalent to supplying a list of one column in the UNIQUE clause for the table and effectively specifies a one-column unique constraint.

Creating a UNIQUE index on the table has the same effect as a unique constraint.

Foreign Keys - Referential Constraints

Use foreign keys to maintain integrity between the contents of related tables.

Note: The tables referenced in a foreign key clause of a table definition must exist prior to the definition of the foreign key (unless the key is in the reference table itself, to ensure referential integrity within a table or the table definition is within a create schema statement and the foreign key constraint refers to a table in the same schema definition statement).

The number of columns listed as FOREIGN KEY must be the same as the number of columns in the primary key of the REFERENCES table, unless columns in an unique constraint are referenced explicitly in a column list. See the CREATE TABLE syntax in the Mimer SQL Reference Manual for details.

The nth FOREIGN KEY column corresponds to the nth column in the primary key of the REFERENCES table, and the data types and lengths of corresponding columns must be identical. Columns may not be used more than once in the same FOREIGN KEY clause.

If the NULL indicator is permitted in a foreign key, then either at least one of the columns in the foreign key is NULL or the values in the foreign key columns must be present in the corresponding primary key columns of the reference table.

A table definition may contain as many FOREIGN KEY references as required. The same column in the table may be used in separate FOREIGN KEY clauses referring to different REFERENCES tables.

Note: A table containing a foreign key reference or referenced in a foreign key must be stored in a databank with either the TRANS or LOG option.

Foreign Key Example

The BOOK_GUEST table has three foreign key references:

 CREATE TABLE BOOK_GUEST (RESERVATION    INTEGER(5),
                          BOOKING_DATE DATE DEFAULT CURRENT_DATE NOT NULL,
                          HOTELCODE      HOTELCODE    NOT NULL,
                          ROOMTYPE       ROOMTYPE     NOT NULL,
                             .
                             .
                          ROOMNO         ROOMNO,
                             .
         FOREIGN KEY (HOTELCODE) REFERENCES HOTEL,
         FOREIGN KEY (ROOMTYPE)  REFERENCES ROOMTYPES,
         FOREIGN KEY (ROOMNO)    REFERENCES ROOMS ON DELETE NO ACTION   )
                             .
                             .
 

These maintain referential integrity as follows:

Specifying ON DELETE

When defining a foreign key constraint it is possible to specify in an ON DELETE clause what action that shall take place if the corresponding record in the referenced table is deleted.

The possible actions are

Check Constraints

Check constraints in table definitions are used to make sure that data in a column in the table fits certain conditions. This section gives three different examples of check constraints.

Note: The first two examples shown below are not used in the example database.
Limit the city for hotels to Stockholm or Gothenburg:
 CREATE TABLE HOTEL (HOTELCODE HOTELCODE,
                     NAME      CHAR(15)  NOT NULL,
                     CITY      CHAR(15)  NOT NULL,
                     OVERBOOK  BOOK_RATE NOT NULL,
       PRIMARY KEY (HOTELCODE),
        CONSTRAINT CITY_CHECK CHECK (CITY IN ('STOCKHOLM','GOTHENBURG')))
       IN HOTELDB;
Prevent blank entries in the HOTELCODE column:
 CREATE TABLE HOTEL (HOTELCODE HOTELCODE,
                     NAME      CHAR(15)  NOT NULL,
                     CITY      CHAR(15)  NOT NULL,
                     OVERBOOK  BOOK_RATE NOT NULL,
        PRIMARY KEY (HOTELCODE),
        CHECK (HOTELCODE <> ' '))
        IN HOTELDB;
 

This check clause extends any limitations imposed by the HOTELCODE domain definition. The extension applies only to this table, and does not affect other columns in the database which belong to the HOTELCODE domain.

The constraint name, CITY_CHECK in the first example above, can be used in an ALTER TABLE statement to drop the check constraint. All constraints, primary key, unique, not null and foreign key constraints can be named in this manner.

If no constraint name is given, a unique name is generated by the system. This name can be seen by using the describe statement in BSQL. See Mimer BSQL.

Make sure that arrival dates are before departure dates:
 CREATE TABLE BOOK_GUEST (   .
                             .
                 ARRIVE          DATE           NOT NULL,
                 DEPART          DATE           NOT NULL,
                             .
                             .
                 CHECKIN         DATE,
                 CHECKOUT        DATE,
                             .
                             .
         CHECK (ARRIVE < DEPART AND CHECKIN <= CHECKOUT))
         IN HOTELDB;

Check conditions allow any value that does not evaluate to false in the check condition. This means that unknown values (the NULL indicator) are allowed in columns restricted by the check condition. Thus, the check condition above does not exclude NULL from the CHECKIN and CHECKOUT columns (NULL values give an unknown result in the condition).



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX