Managing Table Relationships

You can use SQLWizard to relate the information in one table to that in another table. But, to be able to match records, the foreign key in the dependent table must have a column or collection of columns that match primary key columns in a referenced table.

For example, consider the EMPLOYEE and DEPT tables. Each employee must work in a department that exists. If every existing department must have a row in the DEPT table, then a foreign key built on the Dept column of the Employee table references the primary key of the DEPT table, which is the DEPT column.

If you try to assign an employee to a department that does not exist, you will get an error. If you try to delete a department that has employees working in it, you will get an error. Depending on how you define the foreign key, SQLWizard could automatically delete the employees for you.

If you change the Department ID for a department, SQLWizard may cascade these changes to the employees in that department, or display an error message depending on how you define the foreign key.

Referential integrity constraints ensure that data from one table that must match data in another table is not altered inadvertently. If you look at the Create Relationship dialog box you see three options that help you to enforce referential integrity between your tables. Restrict is the default.