Model a constraint in your physical data model to demonstrate the constraints between a guest of a hotel, and their vehicle. Enter the room number of a guest as the role name. Respond to error messages.
A constraint in the physical data model is the equivalent of a relationship line in an ER diagram: it models the relationships between tables just as the relationship line models the relationships between entities.
The constraint can be specified only as identifying or non-identifying. An identifying constraint is an instance of the child table that can only exist if there is a parent table. The primary keys in the parent table will be both primary and foreign keys in the child table.
A non-identifying constraint indicates that an instance of the child table can exist even without a parent table. The foreign keys may be non-key columns in the child table.
To model a constraint
1 In the Tutorial encyclopedia, open the definition dialog for the Guest-to-Vehicle constraint line between Guest and Vehicle.
2 Notice that you can change this line to identifying or non-identifying (child optional) in the dialog.
3 Click the Foreign Keys tab. Notice the role name Guest for the primary key Guest_Number. You entered this value on the relationship in the logical model. You also entered the constraint name there.
4 Click OK to close the dialog.
5 Notice that the primary key of Guest (Guest_Number with the role name Guest) is both primary and foreign key in Vehicle, the child of an identifying relationship. But the primary key of Guest is only a foreign key in Reservation, the child of a non-identifying relationship.
Enter Role Names
A role name is the name of a column in its role as a foreign key in a child table. It is a constraint property.
To enter a role name for Room Number
1 Click the constraint belongs_to between Room and Guest in the physical model Hotel Check-In.
2 Click the Foreign Keys tab.
3 Enter the role name Room_Number in the role name column for the column number.
4 Click OK.
5 Click Dictionary > Update FKs.
Your diagram should now look like the following figure. Notice that the foreign key number in the Reservation table has also changed.
Enter trigger codes and messages
Designate the code and message that is displayed when you make an error on insertion, update and deletion of a record in the child table.
To enter trigger codes and messages to respond to an error message
1 Click the constraint Guest-to-Vehicle between Guest and Vehicle in the physical model Hotel Check-In.
2 Click the Error Codes/Messages tab.
3 Error codes are a numeric value, starting at 20001. In the Parent Delete Code box, enter the number 44321.
4 The message can be any text you want, to a maximum length of 255 characters. Enter this message "You cannot delete this Guest because it still has corresponding Vehicles."
5 Enter in the Child Insert Code box the number 51234. In the corresponding Message box, enter : "You cannot insert a Vehicle without a valid Guest Number."
The dialog box should now look like the picture below.
6 Click OK to close the dialog box, and save the values.
