You are here

Add new comment

CREATE TABLE - create a database table

Syntax

Description:

    CREATE TABLE creates a new empty table in the selectted database from the sructure description in the command.

Datatype

    Datatype is here a type of values known by the system which would be filled into the specifiyed database table column.

    Text data types:

      CHARACTER - textfields
      CHAR - same as CHARACTER

    Numeric datatypes (exact numeric):

      DECIMAL - a number with decimals
      DEC - same as DECIMAL
      NUMERIC - same as DECIMAL, but the number of decimals is ...
      INTEGER - number without decimals (plain numbers), is used without specifying a fieldsize
      because it is defined by the database system
      INT - same as INTEGER
      SMALLINT - number without decimals (plain numbers), is used without specifying a fieldsize
      because it is defined by the database system and uses probably less space than INTEGER, because is designed for small numbers. The format depends on DBMS.

    Numeric datatypes (approximate numeric):

      FLOAT - numbers in exponential form
      REAL - such as FLOAT, but is used without specifying the fieldsize, because it is defined by the database system.
      DOUBLE PRECISION - such as REAL, but probably with doppelten precision
      DOUBLE - same as DOUBLE PRECISION

    Other datatypes, not defined in the standart SQL :

      DATE - datevalue, im most european implementations in the following format: dd.mm.yyyy
      TIME - timevalue, im most european implementations in the following format: hh-mm-ss
      VARCHAR - string with variable length, depending on the impementation maximal 254 to 2048 characters.
      LONG VARCHAR - sting with variable length, depending on the impementation maximal 16 kilobytes.

    Example:

 CREATE TABLE torder (
 onum INTEGER,
 anz  INTEGER );

Creates an empty table torder with records, which have two integer fields
onum and anz.

Size

    Syntax: whole size [,decimals]

    Description:

    Decimals are allowed only with DECIMAL and NUMERIC declarations. The size declaration is allowed only with variable length datatypes. When the size declaration is not given for the variable length fields, then the default value 1 would be used by the system. Exception: for CHARAKTER a size must always be specifyed (no default values).

    Example:

CREATE TABLE tverkauf (
 vnum  INTEGER,
 vname CHAR(30),
 stadt CHAR(20),
 prov  DECIMAL);

Here is an empty table tverkauf with four records (vnum - number, vname - 30 characters, stadt - 20 characters and prov - number) created.

Fieldtype

    Syntax:

    Fieldtype [...]

    Fieldtype is here the type of the table column to be created.

    NOT NULL

      Is needed when, no NULL-Values in the columns are allowed.

    UNIQUE

      Allows only unique Values in the database table column.

    CHECK( logical construct )

      Is used if an error check is needed before new values are saved into the field.

    DEFAULT = Value

      Defines a default value for the column. Empty fields would have the specifyed value and would not be NULL.

    PRIMARY KEY

      Specifyes the primary key for the database table.

    Example:

CREATE TABLE tverkauf (
 vnum  INTEGER NOT NULL UNIQUE PRIMARY KEY,
 vname CHAR(30) NOT NULL UNIQUE,
 stadt CHAR(20) DEFAULT='Berlin',
 prov  DECIMAL CHECK(comm &lt 1) );

Creates a new empty table tverkauf with records with four fields :
vnum - unique, not null number as primary key;
vname - 30 character not null and unique textfield;
stadt - 20 character textfield with default value 'Berlin';
prov - number, which is always smaller than 1.

Categories: