The table definition consists of one or more lines. Each line can be one of the following:
The optional first line starting with a
#provides a description of the table’s purpose. It may also be thought of as the table’s long title.
A new attribute definition in any of the following forms (see Datatypes for valid datatypes):
name : datatype
name : datatype # comment
name = default : datatype
name = default : datatype # comment
---(at least three hyphens) separating primary key attributes above from secondary attributes below.
A foreign key in the format
-> ReferencedTable. (See Dependencies.)
For example, the table for Persons may have the following definition:
# Persons in the lab username : varchar(16) # username in the database --- full_name : varchar(255) start_date : date # date when joined the lab
This will define the table with attributes
start_date, in which
username is the primary key.
Attribute names must be in lowercase and must start with a letter. They can only contain alphanumerical characters and underscores. The attribute name cannot exceed 64 characters.
- Valid attribute names
- Invalid attribute names
Ideally, attribute names should be unique across all tables that are likely to be used in queries together.
For example, tables often have attributes representing the start times of sessions, recordings, etc.
Such attributes must be uniquely named in each table, such as
Secondary attributes can be given default values.
A default value will be used for an attribute if no other value is given at the time the entity is inserted into the table.
Generally, default values are numerical values or character strings.
Default values for dates must be given as strings as well, contained within quotes (with the exception of
Note that default values can only be used when inserting as a mapping.
Primary key attributes cannot have default values (with the exceptions of
CURRENT_TIMESTAMP attributes; see Primary Key).
An attribute with a default value of
NULL is called a nullable attribute.
A nullable attribute can be thought of as applying to all entities in a table but having an optional value that may be absent in some entities.
Nullable attributes should not be used to indicate that an attribute is inapplicable to some entities in a table (see Entity Normalization).
Nullable attributes should be used sparingly to indicate optional rather than inapplicable attributes that still apply to all entities in the table.
NULL is a special literal value and does not need to be enclosed in quotes.
Here are some examples of attributes with default values:
failures = 0 : int due_date = "2020-05-31" : date additional_comments = NULL : varchar(256)