Home » SQL & PL/SQL » SQL & PL/SQL » alter query (oracle 10g)
alter query [message #399826] Fri, 24 April 2009 04:11 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

i am having two tables.i have dropped a foreign key consraint of one table as earlier i got this error.
Error starting at line 22 in command:
DELETE FROM EMRLABTESTLKUP WHERE TEST_ID = 0
Error report:
SQL Error: ORA-02292: integrity constraint (CCHIT_QC.FK_EMRLABRES_EMRLABTESTLKUP) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

i have used this query 
ALTER TABLE EMRLabResults DROP CONSTRAINT FK_EMRLabRes_EMRLabTestLkup

ad now i am adding this query 

ALTER TABLE EMRLabResults ADD
	CONSTRAINT FK_EMRLabRes_EMRLabTestLkup FOREIGN KEY 
	(
		ORDER_ID
	) REFERENCES EMRLABTESTLKUP 
	(
		TEST_ID
	)   
/

now i am facing this error.
Error report:
SQL Error: ORA-02298: cannot validate (CCHIT_QC.FK_EMRLABRES_EMRLABTESTLKUP) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has
           child records.
*Action:   Obvious


how to add that conctarint on particular table
/

Re: alter query [message #399829 is a reply to message #399826] Fri, 24 April 2009 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Delete the orphaned child records or add the missing parent(s).

Dropping a foreign key because it's preventing you from deleting data is a monumentally bad idea. You should have deleted the child records in the first place.
Re: alter query [message #399833 is a reply to message #399826] Fri, 24 April 2009 04:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Suppose you buy an alarm system for your home with a motion detector for the interior.
One day, when you are gone, your neighbor wants to get in. He calls you, so you switch off the alarm. When he is in, you switch the alarm back on.
Now, how surprised would you be that the alarm goes off?
Re: alter query [message #399864 is a reply to message #399826] Fri, 24 April 2009 05:28 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
nice analogy Cool
Previous Topic: query
Next Topic: ORA-12899: value too large for column string (actual: string, maximum: string)
Goto Forum:
  


Current Time: Sun Dec 04 02:21:42 CST 2016

Total time taken to generate the page: 0.10397 seconds