PowerDesigner to create a table constraint

Check Constraint
Check is divided into column constraints and table constraints. A column constraint is a constraint on only one column in a table that can be set in a column's properties, and a table constraint is a constraint on more than one column that needs to be set in the table's properties (in fact, the column constraint can also be set in a table constraint).

standard CHECK Constraint
For some common check constraints, you can do this directly by setting up the interface. Take class tables For example, classname each school has its own naming rules, assuming that classname must start with 2, you need to define a check constraint on the classname column so that it satisfies the naming convention. The specific operation is to double-click the Class table in PD, open Class Properties window, switch to the Column tab, select ClassName Column, click the Toolbar "Properties" button, pop-up ClassName Properties window, switch to Standardchecks tab as shown:



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

Parameter description
The minimum number of Minimum attributes acceptable
Maximum number of Maximum properties acceptable
When the default property is not assigned a value, the system-supplied defaults
Unit units, such as kilometer, ton, yuan
Data display format for the Format property
The assignment of the lowercase property changes to all lowercase letters
The assignment of the uppercase property becomes all uppercase
Cannot modify this property once the assignment cannot be modified
List of Values property assignment lists, except the values in the list, cannot have other values
Label Property list Value

CHECK constraints that directly write SQL statements
In the Front pop-up ClassName Properties window, click the "More" button in the lower left corner, the system will pop more tabs, switch to the "Additional Checks" tab, you can set the constraint name and specific constraint content, as shown in the figure:

Check constraints at the table level are similar to check constraint settings at the column level, 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 contents of the SQL statement.

To Add a unique index:

In the Indexs page of the Table Properties dialog box, add index, select "Unique" in the properties of the custom index and add the Cloumn to the Cloumn page that requires a unique constraint. 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 uniqueness of the corresponding column is more intuitively expressed by the unique constraint, making the corresponding index more clear, so it is generally recommended that you create a unique constraint instead of just creating a unique index.

The operation of creating a unique constraint in PD, in the classroom table, Roomid is the primary key, is necessarily unique, roomname if we also want to go must be unique, then the specific operation is as follows:

In the PD model design panel, double-click the Classrooms table, open the Properties window, switch to the Keys tab, and you can see that there is a row of data pk_classroom, which is a primary key constraint. Add a row of data, named Uq_roomname, you can't select the "P" column on the right, click the toolbar's Properties button, pop the Uq_roomname Properties window, switch to the Column tab, click the Add Column button, and 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.


add UNIQUE constraint: The Join method is the same as the primary key, except that it is not selected as the primary value. Specific methods:
1. In the keys page of the Table Properties dialog box, add an AK (that is, a key name.) Note: Do not select the primary keys!

2. Enter the custom AK property page and add Cloumn to the Cloumn page where you need to set a unique constraint.

. Use rule to create a constraint

Similarly, the class name must start with 2 as an example, and a check constraint is created by 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 will open a Business Rule Properties window, modify the rules name, and modify the type of the rule to constraint. As shown in the figure:



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



The generated script is as follows:

CREATE TABLE Class (
ClassID int NOT NULL,
ClassName varchar is 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 constraints generated by rule and the constraints set in the Check tab create a constraint, and do not affect each other.

Default constraint

The default constraint is when the user has not entered a value, the system gives the default value. The most commonly used is the Createtime field, setting the default value to GETDATE (), which records the creation time when the 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.



The actions for setting default value constraints are as follows: Double-click the schedule, open the Table Properties window, select the Applytime field, click the Toolbar Properties button, open the Column Properties window, switch to the standard Checks tab, and select GETDATE () in the Default Drop-down list box.



At this point all of our constraints in the PD settings are introduced, the next one will introduce views, stored procedures and other database objects.