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 The data model of a personal telephone directory . 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)
   );