Using CREATE TABLE

Use the CREATE TABLE statement to create each table that you design in the data model. This statement has a complicated form, but it is basically a list of the columns of the table. For each column, you supply the following information:

• The name of the column
• The data type (from the domain list you made)

The statement might also contain one or more of the following constraints:

• A primary-key constraint
• A foreign-key constraint
• A not null constraint
• A unique constraint
• A default constraint
• A check constraint

In short, the CREATE TABLE statement is an image, in words, of the table as you drew it in the data-model diagram in Figure 21. The following example shows the statements for the telephone directory data model:

CREATE TABLE name 
   (
   rec_num  SERIAL PRIMARY KEY,
   lname    CHAR(20),
   fname    CHAR(20),
   bdate    DATE,
   anniv    DATE,
   email    VARCHAR(25)
   );

CREATE TABLE child 
   (
   child    CHAR(20),
   rec_num  INT,
   FOREIGN KEY (rec_num) REFERENCES NAME (rec_num)
   );

CREATE TABLE address 
   (
   id_num    SERIAL PRIMARY KEY,
   rec_num   INT,
   street    VARCHAR (50,20),
   city      VARCHAR (40,10),
   state     CHAR(5) DEFAULT 'CA',
   zipcode   CHAR(10),
   FOREIGN KEY (rec_num) REFERENCES name (rec_num)
   );

CREATE TABLE voice 
   (
   vce_num    CHAR(13) PRIMARY KEY,
   vce_type   CHAR(10),
   rec_num    INT, 
   FOREIGN KEY (rec_num) REFERENCES name (rec_num)
   );

CREATE TABLE fax
   (
   fax_num    CHAR(13),
   oper_from  DATETIME HOUR TO MINUTE,
   oper_till  DATETIME HOUR TO MINUTE,
   PRIMARY KEY (fax_num)
   );

CREATE TABLE faxname 
   (
   fax_num    CHAR(13),
   rec_num    INT,
   PRIMARY KEY (fax_num, rec_num),
   FOREIGN KEY (fax_num) REFERENCES fax (fax_num),
   FOREIGN KEY (rec_num) REFERENCES name (rec_num)
   );

CREATE TABLE modem 
   (
   mdm_num    CHAR(13) PRIMARY KEY,
   rec_num    INT,
   b_type     CHAR(5),
   FOREIGN KEY (rec_num) REFERENCES name (rec_num)
   ); 

In each of the preceding examples, the table data gets stored in the same dbspace that you specify for the database because the CREATE TABLE statement does not specify a storage option. You can specify a dbspace for the table that is different from the storage location of the database or fragment the table into multiple dbspaces. For information about the different storage options Informix database servers support, see the CREATE TABLE statement in the IBM Informix: Guide to SQL Syntax. The following section shows one way to fragment a table into multiple dbspaces.

Creating a Fragmented Table

To control where data is stored at the table level, you can use a FRAGMENT BY clause when you create the table. The following statement creates a fragmented table that stores data according to a round-robin distribution scheme. In this example, the rows of data are distributed more or less evenly across the fragments dbspace1, dbspace2, and dbspace3.

CREATE TABLE name 
   ( 
   rec_num  SERIAL PRIMARY KEY,
   lname    CHAR(20),
   fname    CHAR(20),
   bdate    DATE,
   anniv    DATE,
   email    VARCHAR(25)
   ) FRAGMENT BY ROUND ROBIN IN dbspace1, dbspace2, dbspace3;

For more information about the different distribution schemes that you can use to create fragmented tables, see Table Fragmentation Strategies.

Dropping or Modifying a Table

Use the DROP TABLE statement to remove a table with its associated indexes and data. To change the definition of a table, for example, by adding a check constraint, use the ALTER TABLE statement. Use the TRUNCATE statement to remove all rows from a table and all corresponding index data while preserving the definition of the table. For information about these commands, refer to IBM Informix: Guide to SQL Syntax.