 |
 |
 |
Module Reference |  |
 |
 |
|
|
|
Module Reference | |
|
|
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 |
|---|---|
|
True or False |
|
Any Python integer (not Python Long or Decimal) |
|
Any Python long integer between -9223372036854775808 and 9223372036854775807 |
|
Any Python floating point number |
|
A string of 255 characters or less (Not unicode?) [a] |
|
A 24-bit string [b] |
|
A 24-bit binary string [b]] |
|
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] |
|
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] |
|
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.
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)
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.
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.
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.
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.
|
|
|
Module Reference | |
|
|