Use custom types, rules, and default values in SQL and Erwin to implement check constraints to ensure data integrity.

========================================================== ========================
/* Create a rule named list. This rule indicates that the variable can only take values 0 and 1 */
Create rule list
As @ Col in (0, 1)
Go

/* Create a custom data type, which is a child type of the int type */
Exec sp_addtype intswitch, "int", "not null"
/* Bind the rule list to the custom type intswitch */
Exec sp_bindrule list, intswitch
Go

/* Create a table named mytest, where on_or_off is the custom intswitch type */
Create Table mytest (
On_or_off intswitch
)
Go

Run the preceding line in the SQL query analyzer.CodeIn the database, a custom data type named intswitch is generated, which is a subset of the int type. data of this type can only be 0 or 1. At the same time, the database also generates a table named mytest. The table has an intswitch type field on_or_off, which can only take the values 0 or 1 and cannot be empty.
Open the Enterprise Manager of SQL2000 and view the above information in tables, rules, and user-defined types.

================================== ====================================
To cancel the preceding operations:
/* Cancel binding a rule on a custom type */
Exec sp_unbindrule intswitch
/* Delete a Rule */
Drop rule list
/* Ensure that no field uses a custom type before deleting the custom type, you can go to "user-defined type" in Enterprise Manager to check which fields are using this custom type */
/* Set the on_or_off field type to another type in the design view of the table mytest, such as the int type */
/* Delete the custom type */
Exec sp_droptype 'intswitch'

2. Set a domain in Erwin to implement check constraints to ensure data integrity
1. Create a domain named intswitch in domains of Erwin and set its parent domain to number (set in domain parant of the General Tab)
2. Open the SQL Server tab and set whether to allow null values for this field in null option. In the valid dialog box, create a rule named list (if a suitable rule already exists, you can select from the drop-down list ). ---- Give the new rule a name and define the rule in three ways: (1) user-defined, as shown in the preceding example, select "user-Define" and input it by yourself. The input format is @ local variable name expression. (2) In min/MAX mode, set the maximum and minimum values to determine a range; (3) set several valid values in valid value.
3. confirm that a domain named intswitch bound to the rule list will be generated.

Use tool --> forwardenginerr/Schema generation... to export the SQL code. The code above is exactly the same.

3. You can set a default value.
The truth is the same as above.
/* Create a rule */
Create rule list
As @ Col in (0, 1)
Go
/* Create a default value */
Create default switchvalue
As 0
Go
/* Create a custom type */
Exec sp_addtype intswitch, "int", "not null"
/* Bind the created default value to the created custom type */
Exec sp_bindefault switchvalue, intswitch
/* Bind the rule to the custom type */
Exec sp_bindrule list, intswitch
Go

In the same way, to unbind the default value:
Exec sp_unbindefault intswitch
Then, delete the default value:
Drop default switchvalue

It is easier to create a default value for a domain in Erwin. You only need to open a dialog box in defaut, create a new default value, and create a name, such as "switchvalue ", then, enter the default value at "server value -- SQL Server default" on the SQL Server tab.