Use 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 (or a NULL constraint, allowing NULL values)
  • 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 3. 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 IBM® 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.