<< Click to Display Table of Contents >>

Create/Modify Foreign Key

Top Previous Next

This dialog lets you create or modify an existing foreign key. It is available from both Hora and ER Diagrammer.


The above illustration shows the dialog as it appears after the owner and master table have been selected from the Owner and Table name boxes on the left side. You can also enter a table name for the exceptions clause, i.e. rows that do not match the foreign-key constraint.

You should first set the master columns for the foreign key:

Select the owner of the master table from the combo box. The drop-down list contains all valid user names.

Select master table name from the second combo box.

Select primary key or unique constraint name from the third combo box.

The right side of the box contains information about the detail table:

The table name is set by the calling dialog and cannot be changed.

The Available Table Columns box initially contains all columns present in the detail table. You can move them to the Foreign key columns box using the left arrow, remove them using the right arrow, or change the order of columns in a composite foreign key by using the up and down arrows.

Above that box is the Add matching FK columns box. If clicked, it will generate an ALTER TABLE ADD COLUMN statement that will add a column corresponding to the selected master table key if one does not already exist.

Finally, there are various options:

You must specify a delete rule: No action, cascade or set null.

The Deferrable box allows to you to choose when the constraint should take effect. Choices are Immediate, Deferrable (Initially Immediate) and Deferrable (Initially Deferred).

The Status box determines whether or not the constraint is enabled when it is created.

The Exceptions table will contain the rowid's of rows that violate the constraint. It must have been previously defined according to Oracle's rules, using the utlexcpt.sql script.

Press OK to create the constraint. If you modify an existing constraint, Hora drops it and creates a new one with the changed syntax. If doing this raises an exception, Hora re-creates the constraint with the old syntax.