Relationships and Referential Integrity

Creating a New Relationship

You start with choosing one of the three relationships, namely Identifying, Non-Identifying and Self (relationships), by clicking the corresponding button on the menu bar.

Then, drag from any attribute in the parent table to anywhere in the child table; you will then see a link between the two entities formed.

Foreign keys will be automatically added to the child table for you.

Making Changes to a Relationship

You can either

  • double-click on the name of the relationship; or
  • right-click on the name of the relationship, then choose Edit Relationship

Inside the Edit Relationship box, you can define the following:

  • the name for the relationship
  • the constraint name, that must be unique across all the constraint names in your data model
  • relationship type#1: identifying or non-identifying
  • parent optionality / partiality: mandatory or optional
  • child optionality / partiality: mandatory or optional
  • child cardinality#2: one or many
  • match option#3: simple or full

Note

#1: An identifying relationship implies mandatory existence of parent in the relationship (i.e. parent optionality must be mandatory)

#2: if child cardinality is set to one, a trigger function will be created in PostgreSQL database. For MySQL, many is the only option because an appropriate function isn't available yet for MySQL to write such trigger function.

#3: match option: Simple allows some foreign key columns to be null while other parts of the foreign key are not null. Full will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null. (Although SQL93 standard includes MATCH PARTIAL , no DBMS supports that yet; so such option is not available here.)

Referential Integrity

Referential integrity deals with what actions should be applied when data in the referenced column is changed. The referenced column is the primary key in the parent table.

Select the Referential Integrity tab inside the Edit Relationship Box to change the actions applied to Parent On Update and Parent On Delete.

Parent On Update specifies the action to perform when a referenced column in the parent table is being updated to a new value.

Parent On Delete specifies the action to perform when a referenced row in the parent table is being deleted.

The available actions and their general meanings are:

  • NO ACTION: produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows.
  • RESTRICT: produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.
  • CASCADE: delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively
  • SET NULL: set the referencing column(s) in the child table to null
  • SET DEFAULT: set the referencing column(s) in the child table to their default values.

Please refer to the documentation of your DBMS for the exact meaning of different actions.

Removing a Relationship

You can either

  • click on the name of the relationship and then click the Delete button on the menu bar; or
  • right-click on the name of the relationship and then select Delete Relationship