Home » SQL & PL/SQL » SQL & PL/SQL » How to add Oracle constraint to below tables
How to add Oracle constraint to below tables [message #573947] Fri, 04 January 2013 09:03 Go to next message
ravi_oracle_1413
Messages: 2
Registered: January 2013
Junior Member
I have three tables:

A {op_id, op_name, .}
B {b_id, op_id, supplier_id, .}
C {c_id, op_id, op_id2, supplier_id, relation, .}

Table B and C have op_id which is foreign key from table A. In table A op_id is primary key, in table B b_id is primary key and in table C c_id is primary key. In table B supplier_id and op_id may have duplicate records. Now I want to add constraint so that if I delete records from Table B for op_id and if a relationship record exists for op_id in table C then it should not allow me to delete. Is it possible through referential integrity constraint ?
Re: How to add Oracle constraint to below tables [message #573949 is a reply to message #573947] Fri, 04 January 2013 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 22558
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How to add Oracle constraint to below tables [message #573954 is a reply to message #573947] Fri, 04 January 2013 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is possible only if there is an actual referential integrity between C and B.
That is if there can't be an op_id in C that is not in B.

Otherwise, you have to do it with a trigger.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: How to add Oracle constraint to below tables [message #573958 is a reply to message #573954] Fri, 04 January 2013 09:56 Go to previous messageGo to next message
ravi_oracle_1413
Messages: 2
Registered: January 2013
Junior Member
Thanks for reply.

please can you suggest on which column do i need to apply referential integrity for table C and B.



[Updated on: Fri, 04 January 2013 09:57]

Report message to a moderator

Re: How to add Oracle constraint to below tables [message #573960 is a reply to message #573954] Fri, 04 January 2013 10:00 Go to previous messageGo to next message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Fri, 04 January 2013 15:19
It is possible only if there is an actual referential integrity between C and B.
That is if there can't be an op_id in C that is not in B.


It sounds like op_id isn't unique in B so no foreign key is possible.
Re: How to add Oracle constraint to below tables [message #573962 is a reply to message #573960] Fri, 04 January 2013 10:04 Go to previous message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Right, so the only way is a trigger.

Regards
Michel
Previous Topic: LISTAGG
Next Topic: Number conversion
Goto Forum:
  


Current Time: Fri Aug 01 21:55:41 CDT 2014

Total time taken to generate the page: 0.09465 seconds