User's Guide Part III. Using SQL Anywhere Chapter 18. Ensuring Data IntegrityColumn defaults automatically assign a specified value to particular columns when 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 filling columns that contain information such as the date or time a row is inserted, or the user ID of the person entering the information, that is available to the computer automatically.
Using column defaults encourages data integrity, but does not enforce it. Defaults can always be overridden by client applications.
The following default values are supported:
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.
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_orderMODIFY 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".
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_orderMODIFY 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_orderMODIFY order_date DEFAULT NULL
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:
For more information, see the Sybase Central online Help.
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.
The following are just a few examples of when a current date default would be useful:
The current timestamp is used for similar purposes as 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 time 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 Anywhere Data Types".
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 applications 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 the chance of conflicts during the update.
The autoincrement default 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 where 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 engine 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.
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".
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 same data type as 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.
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 "fifteen days from today", which would be entered as
... DEFAULT ( dateadd( day, 15, getdate() ) )