question

OracleApprender avatar image
OracleApprender asked

What are the Referential actions supported by FOREIGN KEY integrity constraint ?

I want to know what are the different referential actions supported by FOREIGN KEY integerity constraint. Please let me know if anyone knows the answer.

oracleindex-constraints
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA avatar image
KillerDBA answered

From a book:

"Foreign key constraints check whether a column value is found in a list of values in a column designated as a primary key column in the same or a different table."

Foreign Keys Discussed More on Foreign Keys

From a practical perspective, FK's:

  1. Ensure that a record relates back to another table in a predefined way. Think parent-child relationship but it's a little more flexible. Each "child" has a valid "parent." For every department, the employees in it are required to be listed in the employee table.

  2. Ensure that certain records can't be deleted if they are still referenced. A "parent" can't be deleted unless asll the children are also deleted. An employee can't be deleted if he's still part of a department. A payroll record can't be deleted if there's a check written for it.

The foreign key doesn't have to be defined on a single field. Let's say we had payroll records and checks. The payroll record might be primary keyed by the employee number and pay period id. The check might have a primary key on the check number but also a foreign key relationship on the emploeey number and pay period id fields.

Let's say you have project records... you might have multiple foreign keys back to the employee table for different roles in the project... project manager, project champion and maybe project sacrificial goat. These could all be foreign keyed, individually, back to the employee table. Note, you don't have to assign the project sacrificial goat until you relly need one, nulls are usually permitted in foreigh key fields but, when assigned, must have a corresponding record in the referenced table. Each of the foreign key relationships is assessed individually.

There's also a CASCADE function on foreign keys. If you delete the referenced (parent) record, all the referencing (children) records can be deleted, too. That strikes me as a little bit dangerous.

Cascade discussed

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You gave me cent percent knowledge on this. I'm very much thankful to you.
0 Likes 0 ·
OracleApprender avatar image
OracleApprender answered

Answer provided by the user KillerDBA is perfect.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.