Home » SQL & PL/SQL » SQL & PL/SQL » Integrity problem with FK (10g 10.1.2.0.4)
Integrity problem with FK [message #339410] Thu, 07 August 2008 08:01 Go to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Hello,

Yesterday i noticed a big integrity problem in our database and i have absolutely no idea about how something like that was possible. Here is what I found:

We have 2 tables with master-detail relationship (Relationship is not cascade). On the detail table we have a FK that link on the PK of the master table. In the past few days, someone was able to delete the master record without deleting the child record and all the FK was still enable? How is this possible???
When looking in the database, the child record still have the value in the column that have the FK on the master table but the master record was not there.

At this point I tried to re-create the record manually just in case my tool was not able to query the master record correctly. So i recrete the record with the same ID than before and commit succesfully, so the record was really not in the database. Tried to delete the record and got error message saying that Child record existe and delete is not allowed. So how someone was able to delete the record and bypassing this error message? Is there a way to force delete even if FK relationship exist?

Anyone have experience something like this before?

Re: Integrity problem with FK [message #339422 is a reply to message #339410] Thu, 07 August 2008 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
someone was able to delete the master record without deleting the child record and all the FK was still enable? How is this possible???

This is not possible.
Check your constraint is in enable validate state.

Regards
Michel
Re: Integrity problem with FK [message #339432 is a reply to message #339410] Thu, 07 August 2008 08:52 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
It`s actually too late to check the status of the FK since after re-creating the master record i was no longer able to delete it so the FK is now valid.

But yesterday i know for sure that the FK was enable when i got the integrity problem. Is it possible to have a FK enable but invalid? If yes, how is it possible?
Re: Integrity problem with FK [message #339437 is a reply to message #339432] Thu, 07 August 2008 09:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
alter your constraint enable novalidate
Re: Integrity problem with FK [message #339480 is a reply to message #339410] Thu, 07 August 2008 10:22 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Just ran a test in development environment to try to reproduce the problem, but even when setting the FK to novalidate it prevent me from deleting the master record

SQL> alter table gav_classe_permis_attribuee modify constraint CLA_REQEXT_FK enable novalidate;

Table altered.

SQL> delete from gav_requete_externe
  2  where id = 39349;
delete from gav_requete_externe
*
ERROR at line 1:
ORA-02292: integrity constraint (NFRST.CLA_REQEXT_FK) violated - child record
found

Re: Integrity problem with FK [message #339485 is a reply to message #339410] Thu, 07 August 2008 10:39 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Ok my previous test was not good, from the documentation:

Quote:

ENABLE NOVALIDATE means the constraint is checked for new or modified rows, but existing data may violate the constraint



So from what i understand you can create a new FK with novalidate and it will not validate existing data in the FK column. But even with novalidate it will prevent you from deleting master record
Re: Integrity problem with FK [message #339498 is a reply to message #339410] Thu, 07 August 2008 11:43 Go to previous message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Ok, i was able to reproduce the problem

1- Disable the FK
2- Drop the master record
3- Enable the constraint using novalidate

So since i know how this kind of thing may happen, I now have to find who did this and why? this will not be easy.

Thank Frank and Michel for your Help


Regards,
Previous Topic: Correlated Subqueries
Next Topic: group by error
Goto Forum:
  


Current Time: Sat Dec 10 01:11:11 CST 2016

Total time taken to generate the page: 0.05304 seconds