Database default values

The default value (default) is data that is automatically inserted in a column that does not specify specific data when the user enters a record. The default value object is similar to the default value specified by the "default" option when the ALTER TABLE or CREATE TABLE command action table, but the default value object can be used with multiple columns or user-defined data types, and its management and application have many similarities to the rules. A column of a table or a user-defined data type can only be bound to a default value.





9.3.1 Create a default value


(1) Create default value using the Create default command

The
Create default command is used to create a default value object in the current database with the following syntax:


CREATE DEFAULT default_name as constant_expression


where the constant_expression clause is the definition of the default value. The constant_expression clause can be a mathematical expression or function, or it can contain a table's column name or other database object.

Example 9-11: Create a birthday default value Birthday_defa.
Create Default Birthday_defa
As ' 1978-1-1 '
Example 9-12: Create a name default value Name_defa.
Create Default Name_defa
As user

(2) Create default values with Enterprise Manager
Select the database object "Defaults" in Enterprise Manager, right-click, and select N "ew Default" from the shortcut menu, which pops up the Create Default Properties dialog box as shown in Figure 9-6. After you enter the default value name and value expression, click OK to complete the creation of the default value.

9.3.2 View default values
(1) Use Enterprise Manager to view default values
Select the "Defaults" object in enterprise Manager to see most of the default values from the taskpad on the right. As shown in Figure 9-7. You can also select the default value to view, right-click, and choose Properties from the shortcut menu, which shows the default Value Properties dialog box shown in Figure 9-8, where you can edit the default value of the
An expression of value. You can modify the default value name in the same way that you modify the rule name, either by using the sp_rename stored procedure or by directly modifying it in the Taskpad window of Enterprise Manager.


(2) Use stored procedure sp_helptext to view default values
Use the sp_helptext stored procedure to view the details of the default values.
Example 9-13: View default value today.
EXEC sp_helptext today

The results of the operation are as follows:
------------------------------------------------------------------
Text
------------------------------------------------------------------
Create default [Dp_date] as GETDATE ()

9.3.3 Defaults binding and untying
When you create a default value, the default value is simply an object that exists in the database and does not work. As with rules, you need to bind default values to database tables or user-defined objects.
(1) Bind with Enterprise Manager to manage defaults
in Enterprise Manager, select the default value for binding settings, right-click, and select the Properties option from the shortcut menu, which appears as shown in Figure 9-8 The default Value Properties dialog box shown.

The "Bind UDTs ..." in the

Figure 9-8. button is used to bind user-defined data types, the Bind Columns ... button is used to bind a table's columns. Click "Bind UDTs ..." in Figure 9-8. button, a dialog box appears with the binding default value shown in Figure 9-9 to the user's custom data type, and click Bind Columns ... button, a dialog box appears, as shown in Figure 9-
10, that binds the default values to the columns of the table. It is convenient to use them to manage the bindings between default values and table columns and user-defined data types.
(2) Bind default values with stored procedures sp_bindefault
Stored procedures sp_bindefault can bind a default value to a column of a table or a user-defined data type. The syntax is as follows:
Sp_bindefault [@defname =] ' default ',
[@objname =] ' object_name '
[, ' futureonly ']
where ' futureonly ' Options are available only when binding defaults to user-defined data types. When this option is specified, a column that uses only this user-defined data type at a later time applies the new default value, and columns that are currently using this data type are unaffected.
Example 9-14: Binding defaults today to the user-defined data type hire_date.
Exec sp_bindefault today, hire_date
results are as follows:
--------------------------------------------------------- The
Default bound to data type.
The new default has been bound to columns (s) of the specified user data type.

(3) Unbind the default value with stored procedure sp_unbindefault
Stored procedure Sp_unbindefault You can unbind a default value from a table column or user-defined data type with the following syntax:
sp_ Unbindefault [@objname =] ' object_name '
[, ' futureonly ']
where the ' futureonly ' option is the same as binding, only for user-defined data types. It specifies that the existing columns defined with this user custom data type remain bound to this default value. If you do not specify this entry, all columns defined by this user-defined data type will also be unbound from this default value.

Example 9-15: Unbind the default value Num_default from the Quantity column of the table products.
EXEC sp_unbindefault ' products. [Quantity] '
The results of the operation are as follows:
--------------------------- ------------------------------
(1 row (s) affected)
Default unbound from table column.
Note: If the column binds a rule and a default value at the same time, then the default value should conform to the rules.
The default value cannot be bound to a column with the default value specified by the default option when creating or modifying a table with the CREATE TABLE or ALTER TABLE command.

9.3.4 Delete default values
You can select the default value in Enterprise Manager, right-click, choose Delete from the shortcut menu, or delete the default value from the default in the current database by using the drop default command
Value. The syntax is as follows:
DROP DEFAULT {default_name} [,... N]
Note: You must unbind the object to which it is bound before deleting a default value.

Example 9-16: Deletes the birthday default value Birthday_defa.
Drop Default Birthday_defa

Summary of this chapter
The application of the data Integrity tool is a feature of SQL Server that manages the flow of information to and from the output system by using specific rules on the database side, rather than controlling the type of information by the application itself, which makes data independent and application an open database system.