Chapter 18: Rose Data Modeler

Setting a Primary Key

If a column is marked as a primary key, it is the identifying column for the table. In other words, it contains the unique values that distinguish the rows from each other. For example, the primary key in an Employee table might be the Social Security number.

To set the primary key for a table:

1.

Right−click the column in the Logical view, and select Open Specification.

2.

Select the Type tab in the Column Specification window.

3.

Select the Primary Key option.

Note that if you set a column as the primary key, the Not Null field is automatically checked and cannot be deselected. Primary keys cannot contain null values.

Adding Constraints

A constraint is a conditional statement that must be true in order for a table to be updated. You can add a constraint either to a domain, as described above, or to a table. Constraints are a way to enforce business rules. An example of using constraints might be checking that the value in a Birthdate field is prior to the current date. You can check that the value in a State field is a valid state abbreviation or that the value in a Gender field is M or F.

Key Constraints

There are three types of key constraints: primary key constraints, unique constraints, and indexes. A primary key constraint ensures that the value entered into a primary key field is not null and is unique. Rose automatically creates a primary key constraint for you when you create a primary key for a table.

A unique constraint ensures that the value entered into a column is unique. Rose automatically creates a unique constraint for you when you select the Unique Constraint check box for a field on the Column Specification window.

An index provides quick access to records by searching only through a list of key columns when searching for rows in the table.

To add a key constraint:

1.

Open the table or Column Specification window.

2.

Select the Key Constraints tab.

3.

631

Chapter 18: Rose Data Modeler

Click New.

4.

Select the type: Primary Key Constraint, Unique Constraint, or Index.

5.

In the Columns list box, select the column(s) to which the constraint applies. Use the Add button to move the selected columns to the Key Columns list box.

6.

Select the Deferrable check box (Oracle and SQL 92 only) if you want to make the constraint deferred. A nondeferred constraint will run at the end of a statement. A deferred, initially immediate constraint will run at the beginning of a transaction. A deferred, initially deferred constraint will run at the end of a transaction.

7.

Select the Unique check box (index constraint) if the index is unique.

8.

Select the Clustered check box if you want to make an index clustered.

9.

In the Fill Factor/PCT Threshold/PCTFree field, optionally enter the free percentage (1–100) of the index. Each DBMS has a different name for this field.

Check Constraints

A check constraint is any constraint other than a primary key, unique, or index constraint. In other words, it is any constraint other than a key constraint. Check constraints are added on the specification window of either a field or table. The constraints themselves are linked to the table, but you can enter them in either location.

To add a check constraint:

1.

632

Chapter 18: Rose Data Modeler

Open the table or Column Specification window.

2.

Select the Check Constraints tab.

3.

Click New.

4.

In the Expression field, enter the SQL statement for the constraint.

5.

If you are using Oracle or SQL 92, you can select the Deferrable option. Nondeferrable constraints are evaluated at the end of the SQL statement. For example, a nondeferrable constraint might be evaluated at the end of an insert statement. Deferrable constraints can be Initially Immediate, in which case they are evaluated at the beginning of the statement. Deferrable constraints can also be Initially Deferred, in which case they are evaluated at the end of the transaction.

Once a check constraint has been added, it appears in the browser underneath the table, and has a stereotype of <<Check>>.

Adding Triggers

A trigger is a SQL procedure that runs upon a specific event. For example, you can set up a trigger to run every time a record is inserted into a specific table. Triggers can be set up to run when a row is inserted, changed, or deleted.

The specifications for a trigger will vary with the DBMS you are using. A trigger will be modeled in the Logical view, under the table to which it applies, and will have the stereotype <<Trigger>>.

To add a trigger:

1.

633

Chapter 18: Rose Data Modeler

Open the Table Specification window.

2.

Select the Triggers tab.

3.

Click New.

4.

Set the Trigger Event:

♦

Select Insert if the trigger should run when a row is inserted.

♦

Select Delete if the trigger should run when a row is removed.

♦

Select Update if the trigger should run when a row is changed. If you select Update, enter the column that should be updated for the trigger to run.

5.

Set the Trigger Type:

♦

Before will run the trigger before the trigger event.

♦

After will run the trigger after the trigger event.

♦

Instead Of will run a view trigger instead of a table trigger. The Instead Of option is available only when creating a trigger for a SQL Server 2000 or Oracle view.

6.

Set the Granularity (Oracle and DB2 only) to Row if the trigger should run after each row is inserted, updated, or deleted; set it to Statement if the trigger should run after the statement has executed.

7.

Set the Referencing check box if you want to set up references in the trigger. Enter the name of the Old Row, which is the name of the row before the trigger executes, and the New Row, which is the name of the row after the trigger executes. In DB2, you can also enter Old Table, which is the name of the table before the trigger executes, and New Table, which is the name of the table after the trigger executes.

8.

Enter a value in the When Clause field if you wish to further refine when the trigger executes. The When Clause is a condition that must be true for the trigger to execute.

9.

Enter the SQL statement for the trigger in the Action Body field.

634