About Referential Integrity

First let me brief about the RF<foreign key> concept.

If you are thorough on this you will not have this doubt.So letz get going:

Referential integrity is about ensuring that relationships between rows in related tables are valid and that you do not accidentally delete or change related data.

When referential integrity is enforced in an Oracle database, you must observe the following rules:
<1>.You cannot enter a value in the foreign key column of the related table if that value does not exist in the primary key of the related table. However, you can enter a null in the foreign key column. For example, you cannot indicate that a job is assigned to an employee who is not included in the EMPLOYEE table, but you can indicate that an employee has no assigned job by entering a null in the JOB_ID column of the EMPLOYEE table.

<2>. You cannot delete a row from a primary key table if rows matching it exist in a related table. For example, you cannot delete a row from the JOBS table if there are employees assigned to the job represented by that row in the EMPLOYEE table. However, if cascading deletes are enabled, you can delete a primary key row; all matching rows in related tables are also deleted.

<3>. You cannot change a primary key value in the primary key table if that row has related rows. For example, you cannot delete an employee from the EMPLOYEE table if that employee is assigned to a job in the JOBS table.

Oracle supports only two kinds of referential integrity:

1. Enabling referential integrity to check values in related tables when you enter data. If the data value is not allowed (as determined by the rules above), the data entry fails and the data is not added to the database.

2. Cascading deletes.

By now you should have got the picture!!!

SOME MORE HINTS:
DELETE removes one or more records in a table, checking referential constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) there will be no problems.  TRUNCATE removes ALL records in a table. It does not execute any triggers.Also, it only checks for the existance (and status) of another foreign key pointing to the table. If one exists and is enabled, then you will get your error. This is true even if you do the
child tables first.

You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.

Final XPLANATION:

QUESTION:Suppose I have the tables:Father(A, B, C),Son(X,Y)
Father.Primary Key (A)
Son.Foreign Key (X) refers Father.primarykey(A)
This implies that "Son" table has child records of the parent table "Father".
Now I'm trying to delete a row using the query:
delete from Father where A = 123;
           jst as you were asking!!
So now what happens?

ANS:Primary and foreign keys are defined the parent/child relationship, you CANNOT delete a record from parent table until it has record in child table.If your parent table R1 has any child record oracle does not allow to delete this parent record
because oracle RESPECTS the parent/child relationship.
:+)
For your future reference you should define keyword ON DELETE CASCADE with your primary key, this key implicitly delete the child records when you try to delete parent record.

But for now the best way is to delete the associative child record of this primary key from the child table then delete it from parent table.

for more IDEAS ::::Also look at how to disable/enable constraints!!!!

A bit too detailed:but hope this helped you.

Channesh

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.