The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.

asked 18 Oct '12, 15:47

javedrehman's gravatar image

javedrehman
256141421
accept rate: 0%


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.

permanent link

answered 18 Oct '12, 16:19

Volker%20Barth's gravatar image

Volker Barth
30.9k309457668
accept rate: 33%

edited 19 Oct '12, 03:15

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22

question asked: 18 Oct '12, 15:47

question was seen: 1,577 times

last updated: 19 Oct '12, 03:15