CREATE TABLE - create a database table

Syntax

CREATE TABLE Tablename
( { Fieldname Datatype [ (Size) ] Fieldtype [...] } [,...] ) ;

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.