Defining Defaults and Rules for Data

A default is a value that SAP ASE inserts into a column if a user does not explicitly enter one. In database management, a rule specifies what you are or are not allowed to enter in a particular column, or in any column that uses a given user-defined datatype.

You can use defaults and rules to help maintain the integrity of data across the database.

You can define a value for a table column or user-defined datatype that is automatically inserted if a user does not explicitly enter a value.

For example, you can create a default that has the value “???” or the value “fill in later.” You can also define rules for that table column or datatype to restrict the types of values users can enter for it.

In a relational database management system, every data element must contain some value, even if that value is null. Some columns do not accept the null value. For those columns, some other value must be entered, either a value explicitly entered by the user or a default entered by SAP ASE.

Rules enforce the integrity of data in ways not covered by a column’s datatype. A rule can be connected to a specific column, to several specific columns or to a specified, user-defined datatype.

Every time a user enters a value, SAP ASE checks it against the most recent rule that has been bound to the specified column. Data entered prior to the creation and binding of a rule is not checked.

You can create sharable inline default objects for default clauses and automatically use the same default object for multiple tables and columns.

As an alternative to using defaults and rules, you can use the default clause and the check integrity constraint of the create table statement to accomplish some of the same tasks. However, these items are specific to each table and cannot be bound to columns of other tables or to user-defined datatypes.

• Create Defaults
  Define a default using create default, then bind the default to the appropriate table column or user-defined datatype using sp_bindefault.
• Drop Defaults
  To remove a default from the database, use the drop default command.
• Create Rules
  Create the rule using create rule, then bind the rule to a column or user-defined datatype using sp_bindrule.
• Drop Rules
  To remove a rule from the database entirely, use the drop rule command.
• Retrieve Information About Defaults and Rules
  Use sp_help with a table name to show the rules and defaults that are bound to columns.
• Share Inline Defaults
  When you create a new inline default, SAP ASE looks for an existing shareable inline default that have the same value in the database belonging to the same user. If one exists, SAP ASE binds this object to the column instead of creating a new default.