User's Guide
   PART 2. Relational Database Concepts
     CHAPTER 14. Ensuring Data Integrity       

Using column defaults

Column defaults automatically assign a specified value to particular columns whenever a new row is entered into a database table, without any action on the part of the client application, as long as no value is specified by the client application. If the client application does specify a value for the column, it overrides the column default value.

Column defaults are useful for automatically filling columns with information, such as the date or time a row is inserted, or the user ID of the person entering the information.

Using column defaults encourages data integrity, but does not enforce it. Defaults can always be overridden by client applications.

Supported default values 

The following default values are supported:

• A string specified in the CREATE TABLE statement or ALTER TABLE statement

• A number specified in the CREATE TABLE statement or ALTER TABLE statement

• An automatically incremented number: one more than the previous highest value in the column

• The current date, time, or timestamp

• The current user ID of the database user

• A NULL value

• A constant expression, as long as it does not reference database objects.

  Creating column defaults

Column defaults can be created at the time a table is created, using the CREATE TABLE statement, or added at a later time using the ALTER TABLE statement.

Example 

The following statement adds a condition to an existing column named id in the sales_order table, so that it is automatically incremented (unless a value is specified by a client application):

ALTER TABLE sales_order
MODIFY id DEFAULT AUTOINCREMENT

     Each of the other default values is specified in a similar manner. For a detailed description of the syntax, see CREATE TABLE statement.

  Modifying and deleting column defaults

Column defaults can be changed or removed by using the same form of the ALTER TABLE statement as used to create defaults. The following statement changes the default value of a column named order_date from its current setting to CURRENT DATE:

ALTER TABLE sales_order
MODIFY order_date DEFAULT CURRENT DATE

Column defaults are removed by modifying them to be NULL. The following statement removes the default from the order_date column:

ALTER TABLE sales_order
MODIFY order_date DEFAULT NULL

  Working with column defaults in Sybase Central

All adding, altering, and deleting of column defaults in Sybase Central is carried out in the Type tab of the column properties sheet.

  To display the property sheet for a column:

1. Connect to the database.

2. Click the Tables folder for that database, and click the table holding the column you want to change.

3. Double-click the Columns folder to open it, and double-click the column to display its property sheet.

     For more information, see the Sybase Central online Help.

  Current date and time defaults

For columns with the DATE, TIME, or TIMESTAMP data type, the current date, current time, or current timestamp may be used as a default. The default specified must be compatible with the column's data type.

Useful examples of current date default 

The following are just a few examples of when a current date default would be useful:

• To record dates of phone calls in a contact database

• To record the dates of orders in a sales entry database

• To record the date a book is borrowed in a library database

Current timestamp 

The current timestamp is used for similar purposes to the current date default, but when greater accuracy is required. For example, a user of a contact management application may have several contacts with a single customer in one day: the current timestamp default would be useful to distinguish these contacts.

The current timestamp is also useful when the sequence of events is important in a database, as it records a date and the time down to a precision of millionths of a second.

For more information about timestamps, times, and dates, see SQL Data Types.

  The user ID default

Assigning a DEFAULT USER to a column is an easy and reliable way of identifying the person making an entry in a database. This information may be required, for example, when salespeople are working on commission.

Building a user ID default into the primary key of a table is a useful technique for occasionally connected users. These users can make a copy of tables relevant to their work on a portable computer, make changes while not connected to a multiuser database, and then apply the transaction log to the server when they return. Incorporating their user ID into the primary key of the table helps to prevent conflicts during the update.

  The autoincrement default

The autoincrement default is useful for numeric data fields. It assigns each new row a value one greater than that of the previous highest value in the column. Autoincrement columns can be used to record purchase order numbers, to identify customer service calls, or other entries where an identifying number is required, but the value of the number itself has no meaning.

Autoincrement columns are typically primary key columns or columns constrained to hold unique values (see Enforcing entity integrity). It is highly recommended that the autoincrement default not be used in cases other than these, as doing so can adversely affect the database performance.

One case when an autoincrement default does not adversely affect performance is when the column is the first column of an index. This is because the server uses an index or key definition to find the highest value.

The next value to be used for each column is stored as a long integer (4 bytes). Using values greater than (2**31 - 1), that is, large double or numeric values, may cause wraparound to negative values, and AUTOINCREMENT should not be used in such cases.

     A column with the AUTOINCREMENT default is referred to in Transact-SQL applications as an IDENTITY column. For information on IDENTITY columns, see The special identity column.

  The NULL default

For columns that allow NULL values, specifying a NULL default is exactly the same as not specifying a default at all. A NULL value is assigned to the column if no value is explicitly assigned by the client when inserting the row.

NULL defaults are typically used when information for some columns is optional or not always available and is not required for the data in the database be correct.

     For more information on the NULL value, see NULL value.

  String and number defaults

A specific string or number can be specified as a default value, as long as the column holds a string or number data type. You must ensure that the default specified can be converted to the column's data type.

Default strings and numbers are useful when there is a typical entry for a given column. For example, if an organization has two offices: the headquarters in city_1 and a small office in city_2, you may want to set a default entry for a location column to city_1, to make data entry easier.

  Constant expression defaults

A constant expression can be used as a default value, as long as it does not reference database objects. This allows column defaults to contain entries such as the date fifteen days from today, which would be entered as

... DEFAULT ( dateadd( day, 15, getdate() ) )