Now that you have added the tables to the zoo database, you will add the foreign key relationships to the database using both the Design Editor and an SQL script. A foreign key is a relation between two tables that ensures that data can only be inserted into certain columns in one table if it exists in the second table.
In our example, a new animal type in table tblAnimalTypes can only be added if the Category is existing as CategoryID in table tblAnimalCategories. This way, you can only add animals that belong to an animal category that is already defined in tblAnimalCategories. You will use the Design Editor to add this relation to the database. Furthermore, this tutorial provides an SQL script that adds the remaining foreign key relations to the database.
Defining a foreign key relationship using the Design Editor
|1.||Make sure that the ZooDBConnect connection is active and click the Design Editor button in the Standard toolbar or press Ctrl+D.|
|2.||In the Online Browser, click the table tblAnimalTypes and drag it into the Design Editor window.|
|3.||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.|
|4.||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.|
|5.||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.|
|6.||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.|
|7.||In the Database Structure Change Script window, click the Execute Change Script button to execute the change script and to commit the new foreign key to the database.|
|8.||Optionally, right click the "FK_CategoryID" entry in the table design and select Add related tables | Referenced Tables from the context menu.|
Adding foreign key relationships via SQL scripts
To speed up the creation of the remaining foreign key relationships for the zoo database, an SQL script is provided in the tutorial folder. This script has already been added to the project earlier in this tutorial.
|1.||Make sure that the ZooDBConnect connection is active.|
|2.||In the Project window, double-click the file create_ZooDB_relationships.sql. The file opens in an SQL Editor window.|
|3.||In the General section of the Properties window, choose "ZooDBConnect" from the Data Source drop-down list. The SQL script is now connected to the ZooDBConnect data source, and the execution buttons in the SQL Editor become active.|
|4.||Click the Save Project button in the Project window or press Ctrl+S to save this data source assignment in the project properties.|
|5.||Click the Execute button or press F5. Foreign key relationships are created between the tables in the zoo database.|
To get a graphical view of the relationships that were just created, right click the "User Tables" folder in the Online browser, and select Show in new Design Editor.
© 2019 Altova GmbH