User's Guide
   PART 2. Relational Database Concepts
     CHAPTER 13. Designing Your Database       

Designing the database table properties

The 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.

  Choosing column names

A column name can be any set of letters, numbers or symbols. However, you must enclose a column name in double quotes if it contains characters other than letters, numbers, or underscores, if it does not begin with a letter, or if it is the same as a keyword.

     See Alphabetical list of keywords.

  Choosing data types for columns

Available data types in Adaptive Server Anywhere include the following:

• Integer data types

• Decimal data types

• Floating-point data types

• Character data types

• Binary data types

• Date/time data types

• User-defined data types

• Java class data types

     For a description of data types, see SQL 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, you must specify the maximum length of a value in the column.

The long binary data type can be used to store 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 Data Types.

NULL and NOT NULL 

If 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, but you should explicitly declare columns 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.

  Choosing constraints

Although the data type of a column restricts the values that are 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. Most CHECK constraints use either the BETWEEN or IN condition.

     For more information about valid conditions, see Search conditions. For more information about assigning constraints to tables and columns, see Ensuring Data Integrity.

Example 

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	—	null	None

If you specify NOT NULL, a column value must be supplied for every row in the table.