Syntax
Description:
CREATE TABLE creates a new empty table in the selectted database from the sructure description in the command.
Datatype is here a type of values known by the system which would be filled into the specifiyed database table column.
Text data types:
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.
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.
Syntax:
Fieldtype [...]
Fieldtype is here the type of the table column to be created.
NOT NULL
UNIQUE
CHECK( logical construct )
DEFAULT = Value
PRIMARY KEY
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 < 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.