Constraints on creating a table using PowerDesigner

CHECK Constraints

CHECK is divided into column constraints and table constraints. Column constraints are only constraints on one column in the table. You can set them in column attributes, table constraints are constraints on multiple columns and must be set in the table attributes (in fact, column constraints can also be set in Table constraints ).

Standard CHECK Constraints

For some common CHECK constraints, you can directly complete them through the settings interface. Take the class table as an example. Each school has its own naming rules. If ClassName must start with 2, you need to define the CHECK constraint on the ClassName column so that it meets the naming rules. The specific operation is to double-click the Class table in PD, open the Class attribute window, switch to the column tab, select the ClassName column, and click the "attribute" button in the toolbar to bring up the ClassName attribute window, switch to the StandardChecks Tab

 

On this tab, you can define the standard check constraints for attributes. The meanings of parameters in the window are as follows:

Parameter description
Minimum acceptable number of Minimum attributes
Maximum number of acceptable Maximum attributes
Default value provided by the system when the Default attribute is not assigned a value
Unit, such as kilometers, tons, and Yuan
Format
All values assigned to the Lowercase attribute are Lowercase letters.
All values assigned to the Uppercase attribute are Uppercase letters.
Cannot modify this attribute Cannot be modified once it is assigned a value
List Of Values attribute value assignment List. No value exists except the value in the List.
Label of the Label attribute List Value

CHECK constraints for writing SQL statements directly

In the ClassName attribute window that appears, click the "More" button in the lower-left corner. More tabs are displayed. Switch to the "Additional Checks" tab to set the constraint name and the specific constraint content,:

 

Table-Level CHECK constraints are similar to column-Level CHECK constraints. Click the "More" button in the lower-left corner of the table Properties window, switch to the Check tab, and set the name of the CHECK constraint and the content of the SQL statement.

Add a unique index:

In the Indexs page of the Table Properties dialog box, add the index, select "unique" in the attributes of the custom index, and add the Cloumn with the unique constraint to the cloumn page.

Unique Constraint

The unique constraint is basically the same as creating a unique index, because when creating a unique constraint, the system creates a unique index and implements the constraint through the unique index. However, the unique constraint intuitively expresses the uniqueness of the corresponding column so that the purpose of the corresponding index is clearer. Therefore, we generally recommend that you create a unique constraint instead of only creating a unique index.

Create a unique constraint operation in PD. In the classroom table, RoomID is the primary key and must be unique. If RoomName is required to be unique, perform the following operations:

On the Model Design panel of PD, double-click the "classroom" table, open the Properties window, and switch to the "" Keys "tab. a row of Data PK_ClassRoom is displayed, which is the primary key constraint. Add a row of data and name it UQ_RoomName. You cannot select the "P" column on the right. Then, click the "properties" button on the toolbar. The UQ_RoomName attribute window is displayed. Switch to the column tab, click the Add column button, select to add the RoomName column to it, and click OK to add the unique constraint.

 

In this way, the system automatically creates a unique constraint.

Add a unique constraint:

The method for adding a primary key is the same as that for adding a primary key, but you do not need to select a primary key value. The specific method is as follows:
1. In the keys page of the Table Properties dialog box, add an AK (that is, a keys name. Note: Do not select a primary key !);

2. Go to the custom accesskey attributes page and add the Cloumn with the unique constraint to the cloumn page.

. Use Rule to create constraints

Similarly, the class name must start with 2 and use Rule to create CHECK constraints. Create a Rule, double-click the Class table, open the table Properties window, switch to the Rules tab, and click "Create a Object". The system will open a business Rule Properties window, modify the rule name and the rule type to Constraint ,:

 

Switch to the Expression tab, set the Rule content to "ClassName LIKE '2% '", and click OK to complete the Rule setting. Switch to the Check tab of the table attribute. "% RULES %" in the default constraint content is used to indicate the content set in the Rule. If we have other CHECK constraints, you do not want to set it in Rule, but in the Check tab. You only need to delete % RULES % to add the CHECK constraint content, and you can also retain % RULES %, then, add an and between % RULES %. For example, if ClassID must be less than 10000, we can set the Check content as follows:

 

The generated script is as follows:

Create table Class (
ClassID int not null,
ClassName varchar (20) not null,
Constraint PK_CLASS primary key nonclustered (ClassID ),
Constraint CKT_CLASS check (classid< 10000 ),
Constraint ClassNameRule check (ClassName LIKE '2% ')
)
Go

As you can see, the CHECK constraint generated by Rule and the constraint set on the Check tab create one constraint, which does not affect each other.

Default Constraint

The default constraint is that the system gives the default value without entering a value. The most common field is the CreateTime field. The default value is getdate (), which records the creation time when a user creates a row of data. For example, if you want to record the course selection time in the Course Selection table, you can set the default value of ApplyTime to the getdate () function.

 

To set the default value constraints, double-click the course selection table to open the table Properties window, select the ApplyTime field, and click the Properties button on the toolbar to open the column Properties window and switch to the Standard Checks tab, select getdate () from the Default drop-down list box.

 

So far, all our constraints have been described in the settings of PD. The next article will introduce database objects such as views and stored procedures.