Batch SQL Example

As presented, these commands could be run through interactive or batch SQL (with SQLWizard).

CREATE TABLE customer (c_no SMALLINT NOT NULL, 
    company CHAR(25), address CHAR(20), city CHAR(15), 
    state CHAR(2), zip CHAR(5), phone CHAR(14), 
    balance MONEY,
PRIMARY KEY (c_no));
CREATE UNIQUE INDEX custpkey ON customer(c_no)
CREATE TABLE employee (e_no SMALLINT NOT NULL, 
    lname CHAR(10), fname CHAR(10), street CHAR(20), 
    city CHAR(15), st CHAR(2), zip CHAR(5), dept CHAR(4), 
    payrate MONEY, com DECIMAL(2,2),PRIMARY KEY (e_no));
CREATE UNIQUE INDEX emppkey ON employee(e_no)
CREATE TABLE products (p_no SMALLINT NOT NULL, 
    description CHAR(18), price MONEY,PRIMARY KEY (p_no));
CREATE UNIQUE INDEX prodpkey ON products(p_no)
CREATE TABLE orders (o_no SMALLINT NOT NULL, 
    c_no SMALLINT, o_date DATE, s_no SMALLINT,
    PRIMARY KEY (o_no), 
    FOREIGN KEY bad_cust (c_no) 
        REFERENCES customer ON DELETE RESTRICT,
    FOREIGN KEY bad_emp (s_no) 
        REFERENCES employee ON DELETE SET NULL);
CREATE UNIQUE INDEX ordpkey ON orders(o_no)
CREATE TABLE items (o_no SMALLINT, p_no SMALLINT, 
    quantity SMALLINT, price MONEY, 
    FOREIGN KEY bad_ord(o_no) 
        REFERENCES orders ON DELETE CASCADE,
    FOREIGN KEY bad_prod(p_no) 
        REFERENCES products ON DELETE CASCADE);

If the tables already exist you can specify the referential constraints using the ALTER TABLE command. This simply requires that you append the appropriate PRIMARY KEY and FOREIGN KEY clauses onto an ALTER TABLE table-name clause. The CREATE INDEX commands would remain the same, but should be issued before the ALTER TABLE ... PRIMARY KEY commands.

Examining the System Catalog

Creating, dropping or altering a table causes the system to automatically update the sysibm.systables and sysibm.syscolumns tables. You can query the system catalog tables for table, view, column and index information. The query results can be formatted into your own reports. See XDB Server System Tables for a description of the system catalog tables.

Optionally, you can use SQLWizard to examine table definitions interactively. In addition, the Catalog Browser function of SQLWizard lists table, view and index definitions.

If a column is indexed, the name(s) of the index or indexes are listed after the column name, separated by a slash (/). Index type indicator codes (see the following table) appear in parentheses after the index name.

Next, the data type of the column is listed. The NOT NULL or NOT NULL WITH DEFAULT clauses specifies the null permission of the column. If omitted, nulls are permitted in the column.