Manage Foreign Key

This window is used for creating and displaying foreign keys. It is one of the forms of the Manage Object window. That section gives general information about this window, including the use of the buttons and menu items.

This window is part of the Administration Component of AQT.

You can activate this window in the following ways:

• from the Database Explorer, click on Create > Foreign Key.
• from the Manage Table window, click on New from the Foreign Key tab.

The window consists of two parts:

• properties of the foreign key
• foreign key columns

What is a Foreign Key?

This described in Understanding Foreign Keys.

Properties of the Foreign Key

To create a foreign key you need to specify the following properties.

• Table. This is the source table for the foreign key relationship.
• Referenced Table. This is the table which is referenced by the foreign key constraint. AQT will assume that the referenced columns will be the primary key columns of this table; these will be given in the right of the bottom grids. If the table does not have a primary key, or you wish to use different columns, click on Specify Key to select these.
• Delete Rule. This specifies what happens when a row is deleted from the target table. This is explained in more detail below.
• Update Rule. This specifies what happens when one of the referenced-columns is updated in the target table. This is explained in more detail below.
• Include Ref Columns. This specifies whether the SQL for creating the foreign key is to include the list of referenced columns. If the referenced columns are the primary key of the referenced table, this column list is optional.

Delete and Update rule can take the following values (depending on your database).

• Restrict. This will prevent the row from being deleted / updated if there are some rows referring to it. In the AQT_Demo example, this means you will not be able to delete a customer from the Customer_Details table while there are some orders (in Order_Detail) that use the customer.
• Cascase. This will delete the row and all the rows that refer to it. In the AQT_Demo example, if you delete a customer from the Customer_Details table, your database will also delete all orders (in Order_Detail) that use the customer.
• No Action. Same as restrict.
• Set Null. This will allow the row to be deleted / updated, any columns that refer to it are set to null. In the AQT_Demo example, if you delete a customer from the Customer_Details table, all orders (in Order_Detail) that use the customer will have their cust_code column set to null.

Specifying the Foreign Key Columns

You use the bottom three grids to specify which columns in the source table map to the columns in the referenced table.

The right grid will have the referenced columns. This will be either the primary key of the Referenced Table, or the columns you specify with Specify Key. You must now specify the Foreign Key columns, which are the columns from the source table that match the referenced columns.

The columns in the source table are given in the left grid. You select or de-select these as being Foreign Key columns by double-clicking them or using the < and > buttons.

MySQL

There are restrictions on creating foreign keys in MySQL.