create table

Define a new table

Syntax

create_table:
        CREATE TABLE table_name (column_def [, column_def]... [, tab_constraint]...)

Description

The CREATE TABLE statement is used to define a table that will be used in creating a database. The CREATE TABLE statement may also include additional information that defines relationships between tables in the same schema definition. A schema may have multiple tables defined. The table_name is a user-specified identifier that names the table. Within the current schema definition, table names cannot be duplicated.

The optional constraint clauses specify constraints (tests) that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is an SQL object that helps define the set of valid values in the table in various ways.

There are two ways to define constraints: table constraints and column constraints. A column constraint is defined as part of a column definition. A table constraint definition is not tied to a particular column, and it can encompass more than one column. Every column constraint can also be written as a table constraint; a column constraint is only a notational convenience for use when the constraint only affects one column.

Components

table_name	The name of the table to be created.
column_def	The name and data type of a column to be created in the new table. For more information on the structure of the column definition components, refer to the Column Definitions section.
tab_constraint	Optional table constraints that can be declared (keys or foreign keys). For more information on table constraints, refer to the Table Constraint Declarations section.

Example

CREATE TYPE money AS decimal(10,2);

CREATE TABLE acctmgr(
    mgrid       CHAR(7 OCTETS) PRIMARY KEY,
    name        CHAR(24),
    hire_date   DATE,
    commission  DECIMAL(4,3)
);

CREATE TABLE patron(
    patid       CHAR(3 OCTETS) PRIMARY KEY,
    name        CHAR(30),
    street      CHAR(35),
    city        CHAR(17),
    state       CHAR(2 OCTETS),
    country     CHAR(2 OCTETS),
    pc          CHAR(10),
    email       CHAR(63 OCTETS),
    phone       CHAR(15 OCTETS),
    mgrid       CHAR(7 OCTETS) REFERENCES acctmgr ON UPDATE CASCADE,
    notes       LONG VARCHAR
);

CREATE TABLE author(
    last_name   CHAR(13) PRIMARY KEY,
    full_name   CHAR(35),    
    gender      CHAR(1 OCTETS),
    yr_born     SMALLINT,
    yr_died     SMALLINT,
    short_bio   VARCHAR(216),
    CONSTRAINT yob_gender_key KEY (yr_born, gender)
);

CREATE TABLE genres(
    text       CHAR(31) PRIMARY KEY
);

CREATE TABLE subjects(
    text       CHAR(51) PRIMARY KEY
);

CREATE TABLE book(
    bookid      CHAR(14 OCTETS) PRIMARY KEY,
    last_name   CHAR(13) REFERENCES author ON DELETE CASCADE ON UPDATE CASCADE,
    title       VARCHAR(105) KEY,
    descr       CHAR(61),
    publisher   VARCHAR(136),
    publ_year   SMALLINT KEY,
    lc_class    CHAR(33),
    date_acqd   DATE,
    date_sold   DATE,
    price       MONEY,
    cost        MONEY,
    notes       LONG VARCHAR
);

CREATE TABLE related_name(
    bookid      CHAR(14 OCTETS) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE,
    name        CHAR(61)
);
    
CREATE TABLE genres_books(
    bookid      CHAR(14 OCTETS) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE,
    genre       CHAR(31) REFERENCES genres
);

CREATE TABLE subjects_books(
    bookid      CHAR(14 OCTETS) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE,
    subject     CHAR(51) REFERENCES subjects
);

CREATE TABLE sale(
    bookid      CHAR(14 OCTETS) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE,
    patid       CHAR(3 OCTETS) REFERENCES patron ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE auction(
    aucid       INTEGER PRIMARY KEY,
    bookid      CHAR(14 OCTETS) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE,
    mgrid       CHAR(7 OCTETS) REFERENCES acctmgr ON UPDATE CASCADE,
    start_date  DATE,
    end_date    DATE,
    reserve     MONEY,
    curr_bid    MONEY,
    bid_time    TIME
);

CREATE TABLE bid(
    aucid       INTEGER REFERENCES auction ON DELETE CASCADE ON UPDATE CASCADE,
    patid       CHAR(3 OCTETS) REFERENCES patron ON DELETE CASCADE ON UPDATE CASCADE,
    offer       MONEY,
    bid_ts      TIMESTAMP
);