update on cascade will do what

[Former Member]

I have a foreign key declared like this on one of the tables:

ALTER TABLE "DBA"."INSURE" ADD CONSTRAINT "profile_key" NOT NULL FOREIGN KEY ( "COMPANY" ASC, "E_COMP" ASC ) REFERENCES "DBA"."profile" ( "company", "e_comp" ) ON UPDATE CASCADE;

does this mean that update of column company and e_comp in profile table will cascade the value to insure table or vice versa. I do not think it is the insure table that will cascade values to profile table since insure is the child table, but key is declared on the insure table so I am little confused. thanks for your help.

[VolkerBarth]

All RI actions are defined on the PRIMARY KEY of the FK relationship and affect, how the FOREIGN KEYs are handled.

EDIT: I hope the following explanation is a better one: The relationship between parent and child table are as follows:

• The relationship itself (i.e. the FOREIGN KEY declaration) is part of the child table's definition.
• The RI action relates to the according operation (i.e. UPDATE/DELETE) done on rows in the parent table.
• The action itself specifies how the related rows in the child table are handled (CASCADE, SET NULL, SET DEFAULT, CASCADE) or if their mere existence prevents the operation in the parent table (RESTRICT).
• For all actions except RESTRICT, the database engine adds system triggers to enforce that action. Obviously, these triggers are declared as row-level triggers on the parent table and contain logic to modify the related rows in the child table.

---

In your sample, modifying values in the columns "company" and/or "e_comp" in the profile table will lead to according modifications in th "insure" table. In constrast, deleting rows in the profile table for which rows in the insure table exist, will be prevented, as the according default action is ON DELETE RESTRICT.

To cite the docs:

You can specify each of the following referential integrity actions separately for updates and drops of the primary key:

RESTRICT Generates an error and prevents the modification if an attempt to alter a referenced primary key value occurs. This is the default referential integrity action.

SET NULL Sets all foreign keys that reference the modified primary key to NULL.

SET DEFAULT Sets all foreign keys that reference the modified primary key to the default value for that column (as specified in the table definition).

CASCADE When used with ON UPDATE, this action updates all foreign keys that reference the updated primary key to the new value. When used with ON DELETE, this action deletes all rows containing foreign keys that reference the deleted primary key.

One might note that the PK of a FK relationship does not need to be a PK of the according parent table, a UNIQUE KEY will do, as well.