Altova DatabaseSpy 2025 Professional Edition

Defining a Foreign Key Constraint

Home Prev Top Next

A foreign key is a relation between two tables that helps preserve the integrity of the database data. For example, when you add a record, a foreign key ensures that a field (column value) can be inserted in one table only if it exists in the second table.

 

In this example, let us create a foreign key constraint that achieves the following: it should be possible to add a new record in table tblAnimalTypes only if the record's Category exists as CategoryID in table tblAnimalCategories.

To define the foreign key relationship:

1.Open the "ZooDB" project created previously and double-click the dbs_ic_connected ZooDBConnect data source to connect to it.

2.Click the Design Editor ic_new-design-editor toolbar button or press Ctrl+D.

3.In the Online Browser, click the table tblAnimalTypes and drag it into the Design Editor window.

4.Right-click the table design and select Insert new | Key | Foreign Key from the context menu. A foreign key is created in the design, having the name <generated>, and the entry is selected for editing.

5.Optionally, change the key name from <generated> to "FK_CategoryID" and press Enter. If you leave the name as <generated>, the key name will be automatically generated by the database.

6.In the Reference column, double-click the "[select the referenced table]" entry, and select "tblAnimalCategories" from the drop-down list that appears. The first column "CategoryID" of the referenced table appears below the table name and there is no need to change this entry.

7.In the Columns column, double-click the "AnimalTypeID" entry that has been inserted by default, and select "Category" from the drop-down list that appears.

dbs_zoo_foreign_key_01

8.In the Database Structure Change Script window, click the Execute Change Script ic_execute-sql button to execute the change script and to commit the new foreign key to the database.

 

To view the new relationship in the Design Editor, right-click the "FK_CategoryID" entry in the table design and select Add related tables | Referenced Tables from the context menu.

© 2018-2024 Altova GmbH