Foreign Keys
A foreign key constraint (also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. The same table or view can contain a foreign key and a referenced key.
You can define multiple foreign keys in a table or view. Also, a single column can be part of more than one foreign key.
The following restrictions are actual for foreign constraints:
- None of the columns in the foreign key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.
- The referenced unique or primary key constraint on the parent table or view must already be defined.
- A composite foreign key cannot have more than 32 columns.
- The child and parent tables must be on the same database. To enable referential integrity constraints across nodes of a distributed database, you must use database triggers.
Creating and Editing a Foreign Key
- In the Database Explorer window, right click a required table.
- Click Edit Table on the shortcut menu.
- On the Table menu, click New Foreign Key.
- Add required columns, select referenced table, and referenced constraint.
-
Click OK.
-or-
- In the Database Explorer window, right-click a required table.
- Click Edit Table on the shortcut menu.
- Switch to the Constraints tab.
- Create a constraint using the shortcut menu.
Note
To create a foreign key, the referenced table should have a unique index, otherwise dbForge Studio will prompt you to create it. Click Yes in the dialog and the unique index will be added.
When the Table Editor is open, you can switch to the Constraints tab, and edit a foreign key using the shortcut menu.
To delete a foreign key, select a required one, and click Delete Constraint on the shortcut menu.
Example of Creating a Foreign Key
Assume you need to ensure that each employee in the Emp table has and will have a valid reference to a manager. That is, for each employee value of field MGR there always will be one of the EMPNO values in the same table. This is best implemented with a foreign key.
- Open the table and create a primary or unique key for field EMPNO as described in topic Unique and Primary Keys.
- Create the foreign key as described above. Select MGR as constraint column, then select EMP as referenced table, and select the unique or primary key you have created in step 1 as the referenced constraint. That’s all you need to do to establish a referential integrity constraint. For a more flexible control over the constraint you may use the following options of the Advanced tab of foreign key editor:
- Clear the Enabled option to prevent constraint from activating. You can enable it later.
- Clear the Validate option to avoid verification of data that the table already contains. Newly added or modified data still will be checked for validity.
- Select the Deferrable option to preserve possibility of deferring check operation until after the transaction is committed.
Want to Find out More?
Overview
All features
Request a demo
