Specifies a value to insert in a column (or in all columns of a user-defined datatype) if no value is explicitly supplied at insert time.
create default [owner.]default_name as constant_expression
is the name of the default. It must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another default of the same name owned by a different user in the current database. The default value for owner is the current user.
is an expression that does not include the names of any columns or other database objects. It cannot include global variables, but can include built-in functions that do not reference database objects. Enclose character and date constants in quotes and use a “0x” prefix for binary constants.
Defines a default value. Now, you need to bind it to the appropriate column or user-defined datatype with sp_bindefault:
create default phonedflt as "UNKNOWN"
sp_bindefault phonedflt, "authors.phone"
The default takes effect only if there is no entry in the phone column of the authors table. No entry is different from a null value entry. To get the default, issue an insert command with a column list that does not include the column that has the default.
Creates a default value, todays_date, that inserts the current date into the columns to which it is bound:
create default todays_date as getdate()
Bind a default to a column or user-defined datatype—but not a Adaptive Server-supplied datatype—with sp_bindefault.
You can bind a new default to a datatype without unbinding the old one. The new default overrides and unbinds the old one.
To hide the source test of a default, use sp_hidetext.
You can create a default only in the current database.
You cannot combine create default statements with other statements in a single batch.
You must drop a default with drop default before you create a new one of the same name; you must unbind a default with sp_unbindefault, before you drop it.
Adaptive Server generates an error message when it tries to insert a default value that is not compatible with the column’s datatype. For example, if you bind a character expression such as “N/A” to an integer column, any insert that does not specify the column value fails.
If a default value is too long for a character column, Adaptive Server either truncates the string or generates an exception, depending on the setting of the string_rtruncation option. For more information, see the set command.
Default definitions are stored in syscomments.
After a default is bound to a column, its object ID is stored in syscolumns. After a default is bound to a user-defined datatype, its object ID is stored in systypes.
To rename a default, use sp_rename.
For a report on the text of a default, use sp_helptext.
If a column has both a default and a rule associated with it, the default value must not violate the rule. A default that conflicts with a rule cannot be inserted. Adaptive Server generates an error message each time it attempts to insert such a default.
If a column does not allow nulls, and you do not create a default for the column, when a user attempts to insert a row but does not include a value for that column, the insert fails and Adaptive Server generates an error message.
Table 7-5 illustrates the relationship between the existence of a default and the definition of a column as NULL or NOT NULL.
Column null type |
No entry, no default |
No entry, default exists |
Entry Is null, No default |
Entry Is null, default exists |
|---|---|---|---|---|
NULL |
Null inserted |
Default value inserted |
Null inserted |
Null inserted |
NOT NULL |
Error, command fails |
Default value inserted |
Error, command fails |
Error, command fails |
You can define column defaults using the default clause of the create table statement as an alternative to using create default. However, these column defaults are specific to that table; you cannot bind them to other tables. See create table and alter table for information about integrity constraints.
SQL92 – Compliance level: Transact-SQL extension.
Use the default clause of the create table statement to create SQL92-compliant defaults.
create default permission defaults to the Database Owner, who can transfer it to other users.
Commands alter table, create rule, create table, drop default, drop rule
System procedures sp_bindefault, sp_help, sp_helptext, sp_rename, sp_unbindefault
