Feature Description
The definition for a FOREIGN KEY has been extended. The new definition can be used wherever the previous (InterBase 4.0) FOREIGN KEY definition was used, i.e., in a CREATE TABLE and ALTER TABLE statement.
The new syntax for defining a FOREIGN KEY
FOREIGN KEY (column [, col ...])
REFERENCES (base-table [, col ...])
[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
provides a mechanism to define the action to be taken on the data in the associated table when updating or deleting the primary key. The actions defined include cascading the change to associated foreign table(s).
Semantics:
Whenever a primary key is changed, the corresponding foreign key is changed as follows:
NO ACTION: The foreign key is left unchanged. This may cause the primary key update to fail due to referential integrity checks. (default)
CASCADE: For ON UPDATE the corresponding foreign key updated to the new value of the primary key, for ON DELETE, the corresponding key is deleted.
SET NULL: The corresponding foreign key is set to NULL. (All the columns of the foreign key are set to NULL).
SET DEFAULT: Every column of the corresponding foreign key is set to its default value. The default value is the one in effect when the referential integrity constraint is defined. When the default for a foreign key column is changed after the referential integrity constraint is set up, the change will not have an effect on the default value used in the referential integrity constraint.
New Security Check for Reference Privileges (SQL/DSQL)
A security check for reference privileges allows the owner of a database to allow or disallow reference to a primary key from a foreign table.
| Privileges Available | Access |
| ALL | SELECT, DELETE, INSERT, UPDATE, and REFERENCE |
| SELECT | Read data |
| DELETE | Delete data |
| INSERT | Write new data |
| UPDATE | Modify existing data |
| EXECUTE | Execute or call a stored procedure |
| REFERENCE | Refer to the primary key |
Note: ALL has been revised to include REFERENCE. ALL will not include REFERENCE in code written for a previous version of InterBase. The reference privilege can be granted to a table or to specified columns. As a minimum, the reference privilege must be granted to all columns of the primary key. When granted to the entire table, columns, which are not part of the primary key, are not affected.
The reference privilege is checked when a foreign key constraint is defined.
The reference privilege is used at runtime to verify that a value entered in a foreign key is contained in the primary key table.