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 :
The Syntax of the CREATE DEFAULT statement is :
Syntax :
1
| 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 :
The Syntax of sp_bindefault is :
1
2
3
4
5
| 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
The Statement
1
| Sp_bindefault city_default, city_datatype. |
Unbinding Defaults :
Defaults can be unbound from a column or user defined datatype using thesp_unbindefault system stored procedure.
The Syntax of sp_unbindefault is :
The Syntax of sp_unbindefault is :
1
2
3
4
| SP_unbindefault object_name For Example : Sp_unbindefault 'authors.city' |
Renaming Rules and Defaults :
The sp_rename system stored procedure can be used for renaming rules and defaults.
The Syntax of sp_rename is :
The Syntax of sp_rename is :
1
| 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.
1
2
3
4
5
| DROP RULE rule_name ( or ) DROP DEFAULT default_name
|
1
2
3
4
5
| CREATE RULE rule_name AS conditional_expression example: 1. CREATE RULE type_rule AS @typerule IN ( 'business' , 'mod_cook' , 'trad_cook' , 'popular_comp' , 'psychology' ) 2. CREATE RULE dept_name_rule AS @deptname NOT IN ( 'accounts' , 'stores' ) 3. CREATE RULE max_price_rule AS @maxprice >=5000 |
Iformation on a rule can be obtained using the sp_help system stored procedure. The text of a rule can be displayed using the sp_helptext system stored procedure with the name of the rule as its parameter.
Binding Rules :
A rule can be bound using the sp_bindrule system stored procedure.
Syntax :
1
2
3
4
| Sp_bindrule rule_name, object_name.ColName For Example : Sp_bindrule type_rule, 'titles.type' |
The restrictions on the use of the rules are as follows:
- Only one rule can be bound to a column or a user defined datatype.
- A rule cannot be bound to system datatypes.
- If a new rule is bound to a column or datatype that is already been inserted in the table, the existing values in the tables do not have to meet the criteria specified by the rule.
- A rule cannot be defined for a system-defined datatype
Unbinding Rules :
A rule can be unbound from a column or user-defined datatype using thesp_unbindrule system stored procedure.
The Syntax of the sp_unbindrule is :
The Syntax of the sp_unbindrule is :
1
2
3
4
| Sp_unbindrule object_name For Example : Sp_unbindingrule 'titles.type'
|