User's Guide Part III. Using SQL Anywhere Chapter 16. Designing Your DatabaseThe database design specifies which tables you have and what columns each table contains. This section describes how to specify each column's properties.
For each column, you must decide the column name, the data type and size, whether or not NULL values are allowed, and whether you want the database to restrict the values allowed in the column.
Column names can be any set of letters, numbers or symbols. However, if the column name contains characters other than letters, numbers, or underscores, or it does not begin with a letter, or it is a keyword (see "Alphabetical list of keywords"), then whenever the column name is used, it must be enclosed in double quotes.
The data types supported by SQL Anywhere include:
For a description of data types, see the section "SQL Anywhere Data Types". The data type of the column affects the maximum size of the column. For example, if you specify SMALLINT, a column can contain a maximum value of 32,767. If you specify INTEGER, the maximum value is 2,147,483,647. In the case of CHAR, the maximum length of a value in the column must be specified.
The long binary data type can be used to hold information such as images (for instance, stored as bitmaps) or word-processing documents in a database. These types of information are commonly called binary large objects, or BLOBS.
For a complete description of each data type, see "SQL Anywhere Data Types".
When the column value is mandatory for a record, you define the column as being NOT NULL. Otherwise, the column is allowed to contain the NULL. value which represents no value. The default in SQL is to allow NULL values; you should explicitly declare columns to be NOT NULL unless there is a good reason to allow NULL values.
For a complete description of the NULL value, see "NULL value". For information on its use in comparisons, see "Search conditions".
Although the data type of a column restricts the values allowed in that column (for example, only numbers or only dates), you may want to further restrict the allowed values.
You can restrict the values of any column by specifying a CHECK constraint. You can use any valid condition that could appear in a WHERE clause to restrict the allowed values, although most CHECK constraints use either the BETWEEN or IN conditions.
For more information about valid conditions, see "Search conditions". For more information about assigning constraints to tables and columns, see the chapter "Ensuring Data Integrity".
The sample database has a table called department, which has columns named dept_id, dept_name, and dept_head_id. Its definition is as follows:
| Column | Data Type | Size | Null/Not Null | Constraint |
|---|---|---|---|---|
| dept_id | integer | --- | not null | None |
| dept_name | char | 40 | not null | None |
| dept_head_id | integer | --- | not null | None |
Notice that "not null" is specified for each column. In this case, data for all columns is required for every row in the table.
The primary key is the column or columns that uniquely identify the rows in the table. If your tables are properly normalized, a primary key should be defined as part of the database design.
A foreign key is a column or set of columns that contains primary key values from another table. Foreign key relationships build one-to-one and one-to-many relationships into your database. If your design is properly normalized, foreign keys should be defined as part of your database design.