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.

NoteTo use the default, you must issue an insert or update command with a column list that does not include the column that has the default.

These restrictions apply:

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.

NoteDefaults cannot be bound to columns and used during the same batch. sp_bindefault cannot be in the same batch as insert statements that invoke the default.

After you create a default, the source text describing the default is stored in the text column of the syscomments system table. Do not remove this information; doing so may cause problems for future versions of Adaptive Server. Instead, use use sp_hidetext to encrypt the text in syscomments. See the Reference Manual: Procedures, and “Compiled objects”.