Altova DatabaseSpy 2024 Professional Edition

In DatabaseSpy, columns that have a foreign key assigned are shown with the dbs_ic_column_foreign icon. The foreign keys themselves are shown with the dbs_ic_constraint_foreignkey icon.

 

For any table that has references to other tables by means of foreign keys, you can identify and select the referenced tables in the Online Browser, as follows:

 

1.In the Online Browser, right-click a foreign key in the "Keys" folder of a table.

dbs_show_referenced_table

2.Select Show referenced table from the context menu. The foreign key as well as the table it references are selected in the Online Browser.

 

Example

Foreign key constraints help preserve the integrity of the database. For example, let's assume that you are exploring a database that stores employee data. The employee details are stored in the Employee table and the department details are stored in the Department table.

 

+----+------------------+--------------------------+---------------+

| ID | Name             | Email                    | DepartmentID  +

+----+------------------+--------------------------+---------------+

| 1  | Toby Hughey      | t.hughey@nanonull.com    | 1             |

| 2  | Mia Dahill       | m.dahill@nanonull.com    | 2             |

| 3  | Fred Weinstein   | f.weinstein@nanonull.com | 1             |

+----+------------------+--------------------------+---------------+

The Employee table

+----+-------------+

| ID | Name        |

+----+-------------+

| 1  | Development |

| 2  | Marketing   |

+----+-------------+

The Department table

Note that the DepartmentID in the Employee table points to the ID of the department in the Department table. This relationship can be enforced through a foreign key constraint between these two columns, so as to ensure that:

 

Each record in the Employee table can reference any of the existing departments in the Department table.

If you ever attempt to add a new employee to the Employee table with a DepartmentID that does not exist in the Department table, the database would return a legitimate validation error (for example, "Foreign key constraint failed").

If you ever attempt to delete a record from the Department table, and if there are Employee records pointing to that Department, the database would return a legitimate validation error.

© 2017-2023 Altova GmbH