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.