Binding defaults

After you have created a default, use sp_bindefault to bind the default to a column or user-defined datatype. For example, suppose you create the following default:

create default advancedflt as "UNKNOWN"

Now, bind the default to the appropriate column or user-defined datatype.

sp_bindefault advancedflt, "titles.advance" 

The default takes effect only if the user does not make an entry in the advance column of the titles table. Not making an entry is different from entering a null value. A default can connect to a particular column, to a number of columns, or to all columns in the database that have a given user-defined datatype.

These restrictions apply:

• A default applies only to new rows. It does not retroactively change existing rows. Defaults take effect only when no entry is made. If you supply any value for the column, including NULL, the default has no effect.

• You cannot bind a default to a system datatype.

• You cannot bind a default to a timestamp column, because Adaptive Server automatically generates values for timestamp columns.

• You cannot bind defaults to system tables.

• Although you can bind a default to an IDENTITY column or to a user-defined datatype with the IDENTITY property, Adaptive Server ignores such defaults. When you insert a row into a table without specifying a value for the IDENTITY column, Adaptive Server assigns a value that is 1 greater than the last value assigned.

• If a default already exists on a column, you must remove it before you can bind a new default. Use sp_unbindefault to remove defaults created with sp_bindefault. Use alter table to remove defaults created with create table.

To bind citydflt to the city column in friends_etc, type:

sp_bindefault citydflt, "friends_etc.city"

The table and column name must be enclosed in quotes, because of the embedded punctuation (the period).

If you create a special datatype for all city columns in every table in your database, and bind citydflt to that datatype, “Oakland” appears only where city names are appropriate. For example, if the user datatype is called citytype, here is how to bind citydflt to it:

sp_bindefault citydflt, citytype 

To prevent existing columns or a specific user datatype from inheriting the new default, use the futureonly parameter when binding a default to a user datatype. However, do not use futureonly when binding a default to a column. Here is how you create and bind the new default “Berkeley” to the datatype citytype for use by new table columns only:

create default newcitydflt as "Berkeley" 
sp_bindefault newcitydflt, citytype, futureonly

“Oakland” continues to appear as the default for any existing table columns using citytype.

If most of the people in your table live in the same postal code area, you can create a default to save data entry time. Here is one, along with its binding, that is appropriate for a section of Oakland:

create default zipdflt as "94609" 
sp_bindefault zipdflt, "friends_etc.postalcode" 

Here is the complete syntax for sp_bindefault:

sp_bindefault defname, objname [, futureonly] 

defname is the name of the default created with create default. objname is the name of the table and column, or of the user-defined datatype, to which the default is to be bound. If the parameter is not of the form table.column, it is assumed to be a user-defined datatype.

All columns of a specified user-defined datatype become associated with the specified default unless you use the optional futureonly parameter, which prevents existing columns of that user datatype from inheriting the default.