Reference Editor is intended for editing the properties of a foreign key constraint, integrity rules, and choosing referenced columns.
To open the Reference Editor, simply double-click a reference on the diagram or select the Properties item from the reference context menu.
The dialog consists of several tabs, each of which is described in details below.
General
This tab allows you to set up the basic reference properties.
Name
You can enter reference name, which will be displayed in the caption in the middle of the reference on the diagram. It is desirable, than name describe relationship role between tables.
Comment
The field to describe the reference role in full.
Parent table
Shows the referenced table's name.
Child table
Shows the referencing table's name.
Generate
Set this option off to exclude the table from the default selection of references in the Database Generation and Database Modification tools.
Joins
This tab allows to choose an active constraint of the referenced table and columns participated in the reference.
Parent Constraints
This allows to select one of the primary key or unique constraints defined on the referenced column. Choosing a constraint will define columns of referenced table that will be used in the reference. The columns on which the selected constraint is defined fill the grid placed on the left side of the dialog.
In the left side of the grid there is list of columns of referenced table. In the right side there is a list of corresponding foreign key columns of the referencing table.
To change assignment of foreign key column of the referencing table:
1. Click on the Child table column cell, that corresponds to one of the columns of the referenced table.
2. The drop-down menu with the list of the referencing table columns will appear.
3. Then click on the required column of the referencing table.
Integrity
In this tab you can change integrity rules for foreign key constraint.
When record in referenced table is changed (updated or deleted), it is possible to automatically modify associated records in the referencing table. This is the function of delete/update rules of the constraint.
For example, there are two tables: Orders and Customers. The Orders table has the foreign key column CustID which refer to the Cusomers table. You can delete a record from the Customers table and corresponding records from the Orders table, using one DELETE statement. It is possible because of cascade delete rule.
You can assign a rule both to update and delete event. In the left side there is list of update rules. In the right side there is list of delete rules. Click on the appropriate list item to change a rule.
Update constraint. Sets a rule that will be executed on update of a referenced table's record
Restrict. Disallow update of the referenced table record if associated records in the child table exist.
Cascade. Update associated records in compliance with referenced table row update.
Set null. Set foreign key columns of associated records to null.
No action. Do nothing with associated records.
Set default. Set foreign key columns of associated records to default column value. This default value can be set in the Table Columns Manager.
Delete constraint. Sets a rule that will be executed on delete of a referenced table's record
Restrict. Disallow delete of referenced table record if associated records in the child table are exists.
Cascade. Delete all associated records.
Set null. Set foreign key columns of associated records to NULL.
No action. Do nothing with associated records.
Set default. Set foreign key columns of associated records to default column value. This default value can be set in the Table Columns Manager.
A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default.
Match Full will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null.
Match Simple allows some foreign key columns to be null while other parts of the foreign key are not null.
Match Partial will be implemented in the future versions of PostgreSQL.
You can also set transaction check mode.
Not deferrable. This controls whether the constraint reference can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Only foreign key constraints currently accept this clause. All other constraint types are not deferrable.
Deferrable initially immediate. In this case constraint will be checked after each statement. The constraint check time can be altered with the SET CONSTRAINTS command inside the transaction block.
Deferrable initially deferred. In this case constraint will be checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command inside the transaction block.
Note
The Note tab allows you to define a description and an annotation for the edited reference. This properties will not affect the physical database, but they can be useful for your diagram development.
Format
In this tab you can choose the color which will be used for displaying edited reference on the diagram. Click on the field to select appropriate color.
See also:
Diagram Objects: Column Editor | Creating a Reference