Creating a Table

The SQL used to create the tblresources table in our database is shown in Listing 15-1.

Listing 15-1. Creating a table

CREATE TABLE tblresources(
    id INTEGER PRIMARY KEY,
    url VARCHAR(255) NOT NULL UNIQUE default '',
    email VARCHAR(70) NOT NULL default '',
    precedingcopy VARCHAR(100) NOT NULL default '',
    linktext VARCHAR(255) NOT NULL default '',
    followingcopy VARCHAR(255) NOT NULL default '',
    target VARCHAR(35) default '_blank',
    category VARCHAR(100) NOT NULL default '',
    theirlinkpage VARCHAR(100) default NULL,
    whenaltered TIMESTAMP default '0000-00-00',
    reviewed BOOLEAN default 0,
    whenadded DATE default '2006-05-05'),

Let's have a look at the details.

To create a table with an autonumber field named id, the data type INTEGER is used in conjunction with PRIMARY KEY. This is equivalent to identifying a field as INTEGER auto_increment PRIMARY KEY in MySQL. In SQLite this field definition is the one exception to the rule that SQLite fields are typeless—otherwise all fields are strings. Creating fields as types other than string helps document the data types you are expecting but will not restrict the value entered. You can put a string into a float type field and a float into a Boolean. Further, specifying the length of a VARCHAR type field will not truncate data that exceeds the defined length. Any length of string can be entered into any field. Otherwise, the syntax for creating a table functions exactly as you might expect.

The field names used in creating this table are self-documenting, but a few comments are in order. A resource won't be displayed until the reviewed field is set to true. The field with the data type TIMESTAMP whenaltered will be maintained using a trigger as will the whenadded field.