Home » SQL & PL/SQL » SQL & PL/SQL » Unique constraint error (10.2.0.2.0 ,windows)
Unique constraint error [message #428225] Tue, 27 October 2009 09:13 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Hi all,

I am facing a unique constraint error in primary key table while trying to insert row in a table.
With knowing the constraint name
in parent table, i would like to find a matching foreign key table and
the corresponding data in child table.

Can anybody provide me the script to find corresponding foreign key in child table ?
and advice me how to solve this unique constraint issue.


Regards,


Re: Unique constraint error [message #428232 is a reply to message #428225] Tue, 27 October 2009 09:26 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
To find the matching fk you need the constraint name unless there is only one fk to that table.
Re: Unique constraint error [message #428235 is a reply to message #428232] Tue, 27 October 2009 09:30 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Yes i know the constraint name .please tell me how to find which foreign key
from child table is restricting me to insert row in parent tables.

Regards,
Re: Unique constraint error [message #428238 is a reply to message #428225] Tue, 27 October 2009 09:36 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
What error are you actually getting?
Because unique key violations have nothing do with child tables.

To find the fk look in user_constraints.
Re: Unique constraint error [message #428244 is a reply to message #428235] Tue, 27 October 2009 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Unique constraint error [message #428268 is a reply to message #428244] Tue, 27 October 2009 12:12 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

Im getting below error while inserting row into table.

ORA-00001: unique constraint (DTH.SYS_C09877733) violated


Regards,
Re: Unique constraint error [message #428271 is a reply to message #428225] Tue, 27 October 2009 12:21 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
So why are you worrying about fk's?
You're violating a constraint on the table you're inserting into, not a related table.
You need to stop inserting data that's already present.

If you need further help post your code along with the table structure and details of the unique constraint - use code tags.
Re: Unique constraint error [message #428272 is a reply to message #428268] Tue, 27 October 2009 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SELECT * FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME = 'SYS_C09877733';

post results from SQL above
Re: Unique constraint error [message #428274 is a reply to message #428271] Tue, 27 October 2009 12:30 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Sorry i cannot post the code as it is confidential.

This error occurred while trying to insert rows into parent table which has primary key.
So i think we need to refer foreign key in the child table for this error.


Please advice.
Re: Unique constraint error [message #428275 is a reply to message #428274] Tue, 27 October 2009 12:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member

>So i think we need to refer foreign key in the child table for this error.

You are mistaken.
UNIQUE constraint prevents duplicate PK
Re: Unique constraint error [message #428276 is a reply to message #428275] Tue, 27 October 2009 12:56 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Thanks for your answer.

May i know the query which would display the respective data of foreign key from
child table which refers to the primary key (constraint_name) of parent table.

Say for example
I want to find the foreign key of child table which refers to the primary key of parent table.

Please provide if there is any script for this.


Regards,
Re: Unique constraint error [message #428277 is a reply to message #428276] Tue, 27 October 2009 13:03 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
At the time you INSERT a new parent row, by definition NO child rows can exist for this yet to be COMMITed parent row.

INSERT of new parent row, has NO impact on child table!

query ALL_CONSTRAINTS for PK/FK relationships
Previous Topic: VIEW
Next Topic: Multi Table Outer Join Help
Goto Forum:
  


Current Time: Wed Dec 07 23:57:56 CST 2016

Total time taken to generate the page: 0.08804 seconds