Universal Database (netDB2) Introductory Guide

Creating A Table

In the following introduction, "<>" means that you should replace what is contained in this <> including <> with your own value. For example, <table_name> should be replaced by the actual table name you want to use.

SQL is not case sensitive(for key words).

To create a simplest table, it is very easy: 

Syntax:   CREATE TABLE <tableName> (
            < list of column definitions including
              column name and data type >
          );

Example:  CREATE TABLE test (
            i integer,
            s varchar(10)
          );

For attributes with other data type, please refer to the data type section of the SQL manuals.

For each column, you can also add any of the following property or column-level constraint:

  • DEFAULT <value> to set the default value.
  • NOT NULL -- the column value can not be null;
If you have all the above definitions, the order to write it is DEFAULT clause the first, then NOT NULL, and other constraints at last. 

Example:  CREATE TABLE accounts (
            owner_name varchar(10),
            acc_num     decimal(7,2) NOT NULL,
            acc_code    integer NOT NULL,
            open_date   date,
            primary key(acc_num)
            );

You can also specify any of the following table-level constraints:

  • UNIQUE
  • DISTINCE (<column name>)
  • PRIMARY KEY(<column name>)
  • FOREIGN KEY(<column names>) REFERENCES <refereced table names>(<column name>)
  • CHECK(<condition>) -- check the constraints be met before data be assigned 
Example:  CREATE TABLE customer (
            ssn       integer NOT NULL,
            name      varchar(20),
            acct      integer,
            age       integer,
            primary key(ssn),  
            foreign key (acct) references accounts(acc_num) );
This particular example has ssn as its primary key and has a foreign key which references the acc_num attribute in the accounts table thereby introducing referential integrity.