create default

Syntax

Changes are in bold.

create [or replace] [owner.] default_name           
as constant_expression

Parameter Changes for create or replace default

create – creates a default if one does not already exist.
or replace – if the default already exists, replaces the default definition with the new definition.
default_name – if the specified default name already exists, then it is replaced with the new default definition. The object name and ID remain the same.
constant_expression – the definition of the default can be changed when the default is replaced. The new default value overrides the old default.

Example

This example creates a default with a phone number defined as UNKNOWN:

create default phonedflt as "UNKNOWN"       

select object_id("phonedflt")      
-----------    
1001051571

This default replaces the previously created default using the or replace clause. The phone number is changed, but the object ID of the default remains the same:

create or replace default phonedflt as "999-999-9999"       

select object_id("phonedflt")      
-----------    
1001051571

Objects Dependent on Replaced Defaults

Many columns can be bound to a replaced default.
User defined datatypes can be bound to the replaced defaults.

Procedures that access these columns will be recompiled when the default is replaced and the procedure is executed.

Permission Changes for create or replace default

Any user who impersonates the default owner through an alias or setuser cannot replace the default.

Changes for replacing a default are in bold.

Granular permissions enabled	With granular permissions enabled, you must have the `create default` privilege. To create a default for another user, you must have the `create any default` privilege. You must be the default owner to replace the default.
Granular permissions disabled	With granular permissions disabled, you must be the database owner, a user with sa_role, or have the `create default` privilege. You must be the default owner to replace the default.

Auditing Changes for create or replace default

Changes are in bold.

Event	Audit Option	Command or access audited	Information in extrainfo
14	create	create default	Roles – current active roles Keywords or options – NULL Previous value – NULL Other information – NULL Current value – NULL Proxy information – original login name, if set proxy is in effect or replace – for create or replace