Definition of a FOREIGN KEY requires that Vantage maintain the integrity defined between the referenced and referencing table.
Vantage maintains the integrity of foreign keys as explained in the following table.
| Data Manipulation Activity | What System Verifies |
|---|---|
| A row is inserted into a referencing table and foreign key columns are defined to be NOT NULL. | A row exists in the referenced table with the same values as those in the foreign key columns. If such a row does not exist, then an error is returned. If the foreign key contains multiple columns, and if any one column value of the foreign key is null, then none of the foreign key values are validated. |
| The values in foreign key columns are altered to be NOT NULL. | A row exists in the referenced table that contains values equal to the altered values of all of the foreign key columns. If such a row does not exist, then an error is returned. |
| A row is deleted from a referenced table. | No rows exist in referencing tables with foreign key values equal to those of the row to be deleted. If such rows exist, then an error is returned. |
| Before a referenced column in a referenced table is updated. | No rows exist in a referencing table with foreign key values equal to those of the referenced columns. If such rows exist, then an error is returned. |
| Before the structure of columns defined as foreign keys or referenced by foreign keys is altered. | The change does not violate the rules for definition of a foreign key constraint. An ALTER TABLE or DROP INDEX statement trying to change such a columns structure returns an error. |
| A table referenced by another is dropped. | The referencing table has dropped its foreign key reference to the referenced table. |
| An ALTER TABLE statement adds a foreign key reference to a table. The same processes occur whether the reference is defined for standard or for soft referential integrity. |
The values in the foreign key columns are validated against columns in the referenced table. The system defines an error table with the following characteristics:
Rows in the referencing table that contain values in the foreign key columns that cannot be found in any row of the referenced table are copied into the error table (the base data of the target table is not modified). Your responsibilities:
|