### 1.4.11.9 CREATE

To create a table in a database:

Syntax

    CREATE TABLE table_name
    (
    column_name1 data_type options,
    column_name2 data_type options,
    .......
    )

Example

This example demonstrates how you can create a table named `Person`, with four columns. The column names will be `LastName`, `FirstName`, `Number`, and `DateOfBirth`:

    CREATE TABLE Person (LastName String, FirstName String, Number String, DateOfBirth Date)

The data_type specifies what type of data the column can hold. The table below contains the data types supported by `web.database`:

Type | Description
--- | ---
``Bool`` | True or False
``Integer`` | Any Python integer (not Python Long or Decimal)
``Long`` | Any Python long integer between -9223372036854775808 and 9223372036854775807
``Float`` | Any Python floating point number
``String`` | A string of 255 characters or less (Not unicode?) [a]
``Text`` | A 24-bit string [b]
``Binary`` | A 24-bit binary string [b]]
``Date`` | Any valid Python `datetime.date` object. Takes values in the form of python `datetime` objects. Only stores days, months and years, any other information is truncated. Dates from `0001-01-01` to `9999-12-31`. [c]
``Time`` | Any valid Python `datetime.time` object. Takes values in the form of python `datetime` objects. Only stores hours, minutes and seconds, any other information is truncated. [c]
``Datetime`` | Any valid Python `datetime.datetime` object. Takes values in the form of python `datetime` objects. Only stores days, months, years, hours, minutes and seconds, any other information is truncated. [c]

[a] Some databases make a distinction between short strings (often named VARCHAR) and long strings (often TEXT). Short string fields are normally faster and so a distinction is also made in this specification.

[b] Although Python supports strings of greater than 24 bit, a lot of databases do not and so in order to be compatible with those databases Binary and String objects should be no longer than 24 bit.

[c] Although Python < 2.3 does not support datetime objects, pure Python compatible libraries exist for Python < 2.3 and these can be used instead so it makes sense to use the standard Python types where possible. The options can be used to further specify what values the field can take. They are described in the next sections.

#### 1.4.11.9.1 REQUIRED

In `web.database`, `REQUIRED` simply means that the field cannot contain a `NULL` value. If you insert a row into a table with a `REQUIRED` field, you must specify a value for the field unless you have also specified the field to have a `DEFAULT` value which is not `NULL` in which case the default value will be used. If you try to set the field to `NULL` an error will be raised.

To create a table with `LastName` and `FirstName` columns where `LastName` could not take a `NULL` value you would use:

    CREATE TABLE Person (LastName String REQUIRED, FirstName String)

#### 1.4.11.9.2 UNIQUE

In `web.database`, a `UNIQUE` field is one in which all values in the table must be different. An error occurs if you try to add a new row with a value that matches an existing row. The exception to this is that if a column is not specified as `REQUIRED`, i.e. it is allowed to contain `NULL` values, it can contain multiple `NULL` values.

To create a table with `LastName` and `FirstName` columns where all the values of `LastName` had to be different or `NULL` you would use:

    CREATE TABLE Person (LastName String UNIQUE, FirstName String)

If a field is specified as `UNIQUE`, `web.database` will not also let you specify a `DEFAULT` value.

Bool, Float, Text and Binary fields cannot be unique.

#### 1.4.11.9.3 PRIMARY KEY

`PRIMARY KEY` columns are unique and cannot take `NULL` values. Each table can only have one field specified as `PRIMARY KEY`.

Primary keys can sometimes be used by `web.database`'s drivers to speed up database queries. A `PRIMARY KEY` column is a column where the value is used to uniquely identify the row.

To create a table with `LastName` and `FirstName` columns where `LastName` is a primary key use:

    CREATE TABLE Person (LastName String PRIMARY KEY, FirstName String)

Bool, Float, Text and Binary fields cannot be primary keys.

#### 1.4.11.9.4 DEFAULT

The `DEFAULT` option is used to specify a default value for a field to be used if a value is not specified when a new row is added to a table.

To create a table with `LastName` and `FirstName` columns where the default value for `LastName` is `'Smith'` we would use:

    CREATE TABLE Person (LastName String DEFAULT='Smith', FirstName String)

You cannot specify a `DEFAULT` if the column is a `PRIMARY KEY` or `UNIQUE`.

If no `DEFAULT` is specified the `DEFAULT` is `NULL`.

Binary and Text fields cannot have default values.

####
1.4.11.9.5 FOREIGN KEY

The final option is `FOREIGN KEY`. If a column is specified `FOREIGN KEY` it cannot have any other options. The table specified as providing the foreign key must have a primary key. It is the primary key value which is used as a foreign key in the other table.

For example:

    CREATE TABLE Houses (House Integer, Owner String FOREIGN KEY=People)

Bool, Float, Text and Binary fields cannot be foreign key fields.

Foreign keys are described in more detail in the section on joins.