Home » SQL & PL/SQL » SQL & PL/SQL » foreign key conflict issue (Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production.)
foreign key conflict issue [message #664562] Sat, 22 July 2017 10:20 Go to next message
orcl2ram
Messages: 4
Registered: July 2017
Junior Member
Dear Mates,
my database Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production.

I am facing the below design issue.
Having table app_users. there is a u_id is the primary key in the table. which contains both internal and external u_ids.
and there is another table Payment and its mod_by column is foreign key referring the u_id column.

There is a requirement like, need to separate the internal users from external users. So, we have separated and created new table based on the main table app_users.

but here, we need same constraints from payment and account tables to newly created internal table.
i.e., mod_by column should refer the internal table column u_id as well.

Issue here is, if we create one more foreign key on mod_by column to refer the newly create table, then that key will check both internal and external users since already one foreign key is there on app_users table. it will pass when that mod_by user id is available in both the tables.

but, my requirement is, it should check should pass the foreign key constraint when that mod_by user id available in any one table.
So, we can not create one more foreign key constraint on mod_by column to refer the internal user table u_id.

For this I have found two approaches.
1. create one associate table and dump both internal and external user ids into that table. and remove the existing constraint on mod_by column and create constraint and refer that to the internal table u_id. So, both internal and external u_ids will be available in common table and constraint will pass.
2. applying if-else condition in all the procedures, to check whether the given u_id available in any internal or external table with out removing the constraint

kindly suggest which one is best or any other alternate way.

Thanks in advance.
Re: foreign key conflict issue [message #664565 is a reply to message #664562] Sat, 22 July 2017 10:39 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

post DDL statements for APP_USERS table including all INDEX & CONSTRAINTS
Re: foreign key conflict issue [message #664572 is a reply to message #664565] Sun, 23 July 2017 04:02 Go to previous messageGo to next message
orcl2ram
Messages: 4
Registered: July 2017
Junior Member
Please find attached ddl doc for your reference. doc contains only necessary columns and constraints and have removed all unnecessary columns.

Thank you.
  • Attachment: ddl.txt
    (Size: 0.52KB, Downloaded 14 times)
Re: foreign key conflict issue [message #664573 is a reply to message #664562] Sun, 23 July 2017 05:02 Go to previous messageGo to next message
John Watson
Messages: 7183
Registered: January 2010
Location: Global Village
Senior Member
Quote:
There is a requirement like, need to separate the internal users from external users. So, we have separated and created new table based on the main table app_users.
I would leave the table unchanged, and create two views: one of internal users, one of external users. That will accomplish the logical separation (I see no reason for the separation to be physical) and your foreign key constraint needs to no change.
Re: foreign key conflict issue [message #664593 is a reply to message #664573] Mon, 24 July 2017 04:25 Go to previous messageGo to next message
orcl2ram
Messages: 4
Registered: July 2017
Junior Member
John, thanks for your reply. But requirement in my project is, we should separate the tables physically. So, two front applications will refer their respective internal and external tables instead of referring same table.
Re: foreign key conflict issue [message #664594 is a reply to message #664573] Mon, 24 July 2017 04:25 Go to previous messageGo to next message
orcl2ram
Messages: 4
Registered: July 2017
Junior Member
John, thanks for your reply. But requirement in my project is, we should separate the tables physically. So, two front applications will refer their respective internal and external tables instead of referring same table.
Re: foreign key conflict issue [message #664596 is a reply to message #664594] Mon, 24 July 2017 05:26 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why is it the requirement though?
It's not a business requirement.
So who thought it was a good idea and why?
Because as far as we can see it's just going to make your life hard.
Re: foreign key conflict issue [message #664597 is a reply to message #664594] Mon, 24 July 2017 05:42 Go to previous messageGo to next message
John Watson
Messages: 7183
Registered: January 2010
Location: Global Village
Senior Member
Quote:
But requirement in my project is, we should separate the tables physically.
In that case you should put the tables into separate databases on different servers.
Re: foreign key conflict issue [message #664598 is a reply to message #664593] Mon, 24 July 2017 06:26 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
orcl2ram wrote on Mon, 24 July 2017 04:25
John, thanks for your reply. But requirement in my project is, we should separate the tables physically. So, two front applications will refer their respective internal and external tables instead of referring same table.
No, that is not a requirement. That is a preconceived (and ill-conceived) technical solution to some other requirement. "Requirement" address business issues, not technical implementations. I can promise you that your business users don't give a flying fig about one table or two.
Re: foreign key conflict issue [message #664599 is a reply to message #664594] Mon, 24 July 2017 07:54 Go to previous message
joy_division
Messages: 4802
Registered: February 2005
Location: East Coast USA
Senior Member
orcl2ram wrote on Mon, 24 July 2017 05:25
John, thanks for your reply. But requirement in my project is, we should separate the tables physically. So, two front applications will refer their respective internal and external tables instead of referring same table.
Ok then, do as John said and just tell them the views are tables and it's most likely no one will ever know.
Previous Topic: oracle external table load
Next Topic: Mutating error
Goto Forum:
  


Current Time: Wed Dec 13 11:49:06 CST 2017

Total time taken to generate the page: 0.02810 seconds