You can create relationships between tables. For example, a typical one-to-many relationship might describe how invoices and invoice line items relate to each other. The relationship might indicate that the invoice_line table is a child of the invoice table, and every row in the invoice_line table relates to exactly one row in the invoice table.
You can create identifying and non-identifying relationships:
In an identifying relationship, the child table cannot be uniquely identified without the parent.
In a non-identifying relationship, the child can be identified independently of the parent.
You could choose to create the invoice and invoice line relationship from the previous example as either an identifying or non-identifying relationship.
If you create an identifying relationship, an invoice line cannot be uniquely identified without also knowing the invoice number it belongs to. For example, assume that invoice line numbers always start at 0 or 1 within each invoice. The same line numbers will appear in different invoices - each invoice will have a line 0, line 1, line 2, etc.
If you create a non-identifying relationship, an invoice can be uniquely identified without knowing the invoice number it belongs to. For example, assume each invoice line has its own unique identifier (invoice_line_id). In this example, invoice_line_id is referred to as a "surrogate key," because it's just a made-up number which has no special meaning in terms of the invoice line.
For this relationship, you would also want to create a unique index on the combination of (invoice_number, line_number) to guarantee there are no two line items with the same line number on the same invoice. In the identifying relationship example, the primary key enforces this rule.
To create a new relationship:
Do one of the following:
To define an identifying relationship, click
in the side toolbar, or press R. The
cursor changes to a +.To define a non-identifying relationship, click
in the side toolbar, or press SHIFT+R.
The cursor changes to a +.
Note: To cancel creating a relationship, press ESC or click a blank area in the playpen.
Click the parent table, then click the child table. A relationship is created between the two tables and is shown as a line.
The mapping between the tables is based on the parent table's primary key. For each column in the primary key of the parent table:
If the child table contains a column with the same name and this is the first relationship between the two tables, the relationship is mapped to the existing column in the child table.
If the child table does not contain a column with the same name, or the child table contains a column that has the same name but the column has a different data type, or a relationship already exists between the tables, a new column is created in the child table. The relationship is mapped to the new column.
To view the columns that are mapped by the relationship, click the relationship link. The mapped columns are shown in red.
You can now define the relationship properties, view the individual column mappings or change the mapping of the child table to the parent table. For more information, see the section called “Modifying a Relationship” .
Note: You can automatically straighten the relationship lines between tables. For more information, see the section called “Straightening Diagram Lines in the Playpen” .
To modify a relationship:
Click a relationship link in the playpen, then click
in the side toolbar. The Relationship
Properties dialog box appears.
Alternate method:
Right-click the relationship link, then click Relationship Properties.
You can enter the following information on the Relationship tab:
In this field ...
Do this ...
Relationship Name
Enter a name for the relationship. When you forward engineer the data model, the relationships are created as foreign key constraints in the target database. These constraints are named based on the relationship name. You can also view a relationship's name in the playpen when you hover over the relationship line.
Relationship Type
Select the type of relationship (identifying or non-identifying).
Cardinality
Select the end cardinality for the primary and foreign keys.
Deferrability
Select the deferrability options.
Not Deferrable - Foreign key constraints are checked immediately at the time an INSERT, UPDATE, or DELETE statement is issued.
Deferrable, Initially Deferred - If the database transaction doesn't specify whether to defer constraint checks, the foreign key constraints will be deferred, meaning that they are not checked until the INSERT, UPDATE, or DELETE transaction is committed.
Deferrable, Initially Immediate - If the database transaction doesn't specify whether to defer constraint checks, foreign key constraints are checked immediately at the time an INSERT, UPDATE, or DELETE statement is issued.
Important: Before selecting an option, read the following description to ensure you fully understand the effect of each option.
When manipulating data in a database (using INSERT, UPDATE, and DELETE statements), the foreign key constraints created by Power*Architect are used to ensure data integrity between the two tables. The deferrability options control when these constraints are enforced.
Within the context of a transaction, deferred constraints are not checked until the transaction is committed, while immediate constraints are checked at the time the INSERT, UPDATE, or DELETE statement is issued (in the middle of the transaction). This means that if you are using immediate constraints, you must be careful about the order in which data is changed. With deferred constraint checking, you can make changes in any order as long as all constraints have been satisfied by the time you commit.
For databases that support deferred and immediate constraint checking, each transaction can specify whether to defer constraint checks or carry them out immediately. If a transaction does not specify this option, each deferrable foreign key constraint is evaluated according to its "initially immediate" or "initially deferred" option. On the other hand, constraints marked as "not deferrable" will always be checked immediately regardless of the transaction's setting.
Important Notes:
For data manipulation done outside the context of a database transaction, there is no difference between immediate constraint checking and deferred constraint checking.
Not all database platforms support this option. Some only support deferred constraint checking, while others only support immediate. When Power*Architect forward engineers to these types of systems, the DDL script includes comments warning about this lack of support.
Delete Rule
Restrict - Prevents deletion of a referenced row.
No Action - If any referencing rows still exist when the constraint is checked, an error raised; this is the default behavior if you do not specify anything. (The essential difference between two choices is that No Action allows the check to be deferred until later in the transaction, whereas Restrict does not.)
Casecade - When a referenced row is deleted, row(s) referencing it should be automatically deleted as well.
Set Null - Cause the referencing columns to be set to nulls when the referenced row is deleted.
Set Default - Cause the referencing columns to be set to default values when the referenced row is deleted.
NOTE: Set Default and Set Nulls do not excuse you from observing any constraints. For example, if an action specifies Set Default but the default value would not satisfy the foreign key, the operation will fail.
Update Rule
Analogous to Delete Rule there is also Update Rule which is invoked when a referenced column is changed (updated). The possible actions are the same.
Platform Supports
Oracle - Supports only Restrict, No Action in the Update Rules and everything except Set Default in the Delete Rules.
PostgreSQL - Supports every rule.
MySQL - Supports everything except Set Default in both the Update and Delete Rules.
IBM DB2 - Supports only Restrict, No Action in Update Rules and everything except Set Default in Delete Rules.
HSQLDB - Supports everything except Restrict in both Update and Delete Rules.
SQL Server 2000 - Supports only Casecade and No Action in both Update Rule and Delete Rule.
SQL Server 2005 - Supports everything except Restrict in both Update and Delete Rule.
On the Mappings tab, you can change the mapping to the child table. Click and drag the relationship link to the column in the child table that is mapped to the parent table.
Note: If a column in the child table has [FK] beside it, this means the column is a foreign key in another parent table. This alerts you that the column is already "in use", since you wouldn't normally use the same column as a foreign key in multiple tables.
Click OK.