Adding Constraints in PowerDesigner

Unique constraint

A unique constraint is essentially the same as creating a unique index, because when a unique constraint is created, the system creates a unique index that implements the constraint through a unique index. However, the unique constraint more intuitively expresses the uniqueness of the corresponding column, making the purpose of the corresponding index clearer, so it is generally recommended to create a unique constraint instead of just creating a unique index.

The operation to create a unique constraint in PD, in the classroom table, Roomid is the primary key, must be unique, roomname if we are going to have to be unique, then the specific operation is as follows:

In the model design panel of PD, double-click the Classroom table, open the Properties window, switch to the Keys tab, and see a row of data pk_classroom, which is a primary key constraint. Add a row of data, named Uq_roomname, you cannot select the "P" column on the right, then click the toolbar's Properties button, pop up the uq_roomname Properties window, switch to the Columns tab, click the Add Column button, choose to add the Roomname column to it, Then click OK to complete the addition of the unique constraint.

This allows the system to automatically create a unique constraint.

Check Constraint

Check is divided into column and table constraints, which are constraints on only one column in a table, can be set in the properties of a column, and table constraints are constraints on multiple columns that need to be set in the properties of the table (in fact, column constraints can also be set in a table constraint).

1. Standard CHECK constraints

For some common check constraints, you can do this directly through the Setup interface. Taking the class table as an example, classname each school has its own naming convention, assuming that classname must start with 2, the check constraint needs to be defined on the ClassName column so that it satisfies the naming convention. To do this, double-click the Class table in the PD, open the class's Properties window, switch to the Columns tab, select the ClassName column, click the Properties button of the toolbar, pop up the classname Properties window, switch to the Standardchecks tab

In this tab you can define the properties of the standard check constraints, the meaning of the parameters for each item in the window, as follows:

Parameters	Description
Minimum	The minimum number of properties that can be accepted
Maximum	The maximum number of properties that can be accepted
Default	System-supplied default value when the property is not assigned a value
Unit	Units, such as kilometers, tons, yuan
Format	Data display format for properties
lowercase	Property assignment to all lowercase letters
Uppercase	The assignment of the property becomes all uppercase
Cannot modify	Once the property is assigned, it cannot be modified again
List of Values	Property assignment list, except for the values in the list, cannot have other values
Label	Label for attribute list value

2. Directly writing a check constraint for an SQL statement

In the front pop-up classname Properties window, click the "More" button in the lower left corner, the system will pop up more tabs, switch to the "Additional Checks" tab, you can set the constraint name and the specific constraint content:

The check constraint at the table level is similar to the column-level CHECK constraint, click the More button in the lower-left corner of the Table Properties window, switch to the Check tab, set the name of the check constraint, and the contents of the SQL statement.

3. Creating a constraint using rule

Similarly, the class name must start with 2 as an example to create a check constraint through rule. First you need to create a rule, double-click the Class table, open the table's Properties window, switch to the Rules tab, click the "Create a Object" button, the system opens a Business Rule Properties window, modifies the rule name, and modifies the type of the rule to constraint:

Then switch to the Expression tab and set the rule to "ClassName like ' 2%" and click the OK button to complete rule settings. Switch to the Check tab of the table properties, where "%rules%" in the default constraint content is used to represent what is set in rule, and if we have some other check constraint content that we do not want to set in rule, but set in the Check tab, you only need to delete the% rules% Add the check constraint content, or you can keep the%rules%, and then add an and between the%rules%. For example, ClassID must be less than 10000, then we can set the check content as follows:

The resulting script is as follows:

CREATE TABLE Class (ClassID int NOT NULL, ClassName varchar () n OT NULL, constraint Pk_class primary key nonclustered (ClassID), constraint ckt_class check (classid<10000), C Onstraint classnamerule Check (ClassName like ' 2% ')) Go

As you can see, the check constraints generated by rule and the constraints set on the Check tab create a constraint that does not affect each other.

Default Constraints

The default constraint is that the system gives the default value if the user does not enter a value. The most common is the Createtime field, which sets the default value to GETDATE (), which records the creation time when a user creates a row of data. For example, to select a schedule, you need to record the time of the selected course, you can set the default value of Applytime to the GETDATE () function.

To set a default value constraint, do this: double-click to select a schedule, open the Table Properties window, select the Applytime field, click the toolbar's Properties button, open the Properties window for the column, switch to the standard Checks tab, and choose getdate () in the default drop-down list box.

Now that all of our constraints have been set up in PD, the next article will cover database objects such as views, stored procedures, and so on.

Adding Constraints in PowerDesigner