| foreign key conflict issue [message #664562] |
Sat, 22 July 2017 10:20  |
 |
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 #664573 is a reply to message #664562] |
Sun, 23 July 2017 05:02  |
John Watson
Messages: 8928
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 #664596 is a reply to message #664594] |
Mon, 24 July 2017 05:26 |
cookiemonster
Messages: 13919
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 |
John Watson
Messages: 8928
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 #664599 is a reply to message #664594] |
Mon, 24 July 2017 07:54 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
orcl2ram wrote on Mon, 24 July 2017 05:25John, 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
