SQL Server : Defaults and Rules

Previous Articles - SQL Server : Rules and Default : Rules

Defaults :

Default is a constant value assigned to a columns=, into which the user need not insert values. A default can be bound to a column or a user-defined datatype.

The Syntax of the CREATE DEFAULT statement is :

Syntax :

 CREATE DEFAULT default_name AS constant_expression

Any constant, built-in function, mathematical expression or a global variable can be used in the constant expression. The character and date constants must be included in single quotation marks ('), whereas money, ineger, and floating-point constants can be specified without quotation marks.

Binding Defaults:

The sp_binddefault system stored procedure is used for binding a default.

The Syntax of sp_bindefault is :

 Sp_bindefault default _name, 'object_name.ColName'

For Example :
 1. CREATE DEFAULT city_default AS 'Mumbai'
 2. Sp_default city_default, 'authors.city'

Create a default, city_default, and binds the default value, Mumbai, to city column of the authors table.
The Statement

 Sp_bindefault city_default, city_datatype.

Binds the default city_default to the user-defined datatype, city_datatype.

Unbinding Defaults:

Defaults can be unbound from a column or user defined datatype using the sp_unbindefault system stored procedure.

The Syntax of sp_unbindefault is :

 SP_unbindefault object_name

For Example :
 Sp_unbindefault 'authors.city'

Unbinds the default specified on the city column of the authors table.

Renaming Rules and Defaults :

The sp_rename system stored procedure can be used for renaming rules and defaults.

The Syntax of sp_rename is :

  sp_rename old_object_name, new_object_name

Dropping Rules and Defaults :

The DROP RULE and DROP DEFAULT statement can be used to drop a rule and default respectively. A rule or default must be unbound from the column or the user-defined data type before it is dropped.

 
   DROP RULE rule_name

           (or)

    DROP DEFAULT default_name