7.5 Constraint Code
A logical database model diagram can display primary key, foreign key, uniqueness, and mandatory column constraints, as well as indexes. Any other constraints in the logical model can be viewed only textually in a code window . If an ORM constraint maps to a check clause , trigger , or stored procedure , you can open a code window to view the constraint code generated. In those few cases where the tool does not generate the code for an ORM constraint, you can use a code window to manually add the relevant code to the logical model.
To view or edit a check clause on a single column , proceed as follows .
Select the relevant table on the diagram, and open its Database Properties dialog.
Open the Columns pane, and select the relevant column. For example, Figure 7-22 highlights the sexCode column of the Patient table in Figure 7-20.
Figure 7-22: Columns pane of the Database Properties dialog for Patient.
Press the Edit button to open the Column Properties dialog for that column and choose the Check pane. If the column has a value constraint, this is displayed by default using the ORM value constraint dialog (see Figure 7-23).
Figure 7-23: Check pane of the Column Properties dialog for sexCode.
Select the radio button: Show check clause code . To edit or delete the code, press the Customize button, which then becomes a Delete button (Figure 7-24).
Figure 7-24: Viewing the body of the check clause code for sexCode.
If you do not press the Customize button, the name and body of the check clause are displayed in read-only format (grayed-out). The Physical name field displays the name that is automatically generated for the check clause (e.g., PatientsexCode_Chk). The Check clause field shows just the body of the clause. For our sexCode example, the actual check clause that will appear when the DDL is generated is:
constraint "PatientsexCode_Chk" check ("sexCode" in ('M','F'))
To view or edit a multi-column check clause , proceed as follows.
Select the relevant table on the diagram, and open its Database Properties dialog.
Open the Check pane. The ORM verbalization of the constraint appears in the Check clauses field, and the code body for the check clause appears in the Preview field. Figure 7-25 shows this for the Patient table in Figure 7-20. This code enforces the ORM subset constraint that each patient with a second given name must also have a first given name.
Figure 7-25: Table-level Database Properties Check pane for the Patient table.
To edit the check clause, either press the Edit button or double-click in the Check clauses field. The body of the check clause is now displayed in the Code Editor , ready for editing (see Figure 7-26).
Figure 7-26: The body of the check clause for the subset constraint in Patient.
For our example, the actual check clause that will appear when the DDL is generated is:
alter table "Patient" add constraint Patient_subset check (("firstGivenName" is not null) or ("secondGivenName" is null))
As Figure 7-25 shows, the Check pane of the Database Properties dialog also includes an Add button for adding a check clause in the Code Editor and a Remove button for deleting a check clause. The Triggers pane of the Database Properties dialog may be used to view or edit triggers on the table.
If a logical model includes stored procedures, these can only be viewed via the Code window . This window also provides access to check clause and trigger code associated with the model. To open the Code window, choose Database > View > Code from the main menu. To open the Code Editor for any listed entry, simply double-click it. You can also use the code window to add new code and delete existing code.
The code window for our Patient example is shown in Figure 7-27. This contains one single-column check clause and one multi-column check clause. Single-column check clauses are depicted with a CC icon. Other rules are depicted with the Ru icon. If a check clause does not appear in the code window, you may need to first generate the physical DDL from the model to make it appear.
Figure 7-27: The Code window allows all code for the model to be viewed and edited.
A more comprehensive example is shown in Figure 7-28, which displays the code window for the sample Employee Database model that ships with the product. To access this model, choose the following options from Visio's main menu: File > New > Browse Sample Drawings > Database > Employee Database.vsd . If the DBMS is SQL Server, the Global Code section includes a stored procedure to enforce an exclusion constraint that spans two tables. The Local Code section includes several single-column and multi-column check clauses. You may double-click any item to view or edit its code in the Code Editor.
Figure 7-28: The code window for the Employee sample database model.
If an ORM constraint cannot be enforced in the target DBMS as a simple declarative constraint (e.g., primary key, foreign key, not null, unique, or simple check clause), the tool usually attempts to enforce the constraint by generating triggers or stored procedures, depending on the DBMS. Although generated code for a stored procedure contains the logic to enforce the constraint, you still need to manually write code to execute the procedure or transform it into appropriate triggers.
For those DBMSs that do not support stored procedures (e.g., Microsoft Access), stored procedures are documented in the DDL as comments, which you can use to help develop an alternative way to enforce the constraints.
ORM constraints that generate trigger or stored procedure code include constraints that span tables (e.g., external uniqueness and inclusive-or constraints spanning roles that map to separate tables), frequency constraints, some ring constraints, and some setcomparison (subset, equality, exclusion) constraints.
Ring constraints were discussed in section 5.8. An irreflexive ring constraint whose roles map to columns in the same table is enforced as a simple check constraint. For example, the mapping in Figure 7-29 shows the ORM constraint no Academic monitors itself mapping to a check clause with the c ondition academicEmpNr <> monitorEmpr . For visualization purposes, the relevant role names and check condition are displayed in text boxes, using superscripts to cross-reference the rule to its referents .
Figure 7-29: The irreflexive ring constraint maps to a simple check clause.
But now consider the mapping of the parenthood fact type shown in Figure 7-30, which is an extension of an example discussed in Chapter 5. Again, roles names are added in text to aid visualization. The frequency constraint indicates that a child may have at most two parents, and the ring constraints declare that parenthood is both asymmetric and intransitive . None of these constraints are mapped to check clauses.
Figure 7-30: The ring and frequency constraints do not map to check clauses.
If SQL Server is the target DBMS, each of these constraints is mapped to a stored procedure, which you can view in the Code Editor. The constraint code generated (minus comments) is as follows.
Create Procedure sp_Parenthood_freq1 as if (not exists (select * from "Parenthood" group by "Parenthood"."childld" having count( * ) > 2)) return 1 else return 2 Create Procedure sp_Parenthood_ring2 as if (not exists (select * from "Parenthood" X, "Parenthood" Y where X."parentld" = Y."childld" and X."childld" = Y."parentld") and not exists (select * from "Parenthood" X, "Parenthood" Y, "Parenthood" Z where X."childld" = Y."parentld" and Y."childld" = Z."childld" and X."parentld" = Z."parentld")) return 1 else return 2
The irreflexive constraint discussed earlier was enforced as a simple check clause, since its condition for any given row could be checked by looking at that row alone. This is not true for the frequency constraint and the asymmetric and intransitive ring constraints. Instead of a check clause, the tool generates stored procedures for these more complex constraints. If a procedure returns 1, then the constraint is satisfied; if it returns 2, the constraint is violated. These procedures could be expensive, so are not run automatically. It is your responsibility to decide how to use them. For example, you might write code to run the procedures, or you might replace the procedures by equivalent triggers.
Notice that the composite (as, it) ring constraint maps to a single stored procedure, with the first existential subquery checking asymmetry, and the second existential subquery checking intransitivity.
In reality, the parenthood relation is not just asymmetric but acyclic . Acyclicity is different from the other ring constraints because it involves recursion, so might be quite expensive to enforce. Some modern DBMSs, such as DB2 and the Yukon release of SQL Server, include the capability of performing recursive queries but not all DBMSs do so. Moreover, the SQL syntax used to support recursion may differ among DBMSs. If you wish to declare an acyclic ring constraint, the tool currently does no more than generate a comment for it, so you have to look after coding this yourself. Moreover, if you choose a composite ring constraint that includes acyclicity, the whole code for the composite constraint is simply a comment. You can view the comment in the Code Editor and edit it like any other code.