| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
04-09-10, 06:49
|
|
Registered User
|
|
Join Date: Oct 2002
Posts: 35
|
|
|
Two foreign keys, one must have a value the other not
|
|
Hello,
We have a table PERSONEL and 2 parent tables, say DepA and DepB.
now PERSONEL has 2 foreign key columns, one for each parent table.
PERSONEL
-----------
ID NUMBER(9)
FK_DEPA NUMBER(9)
FK_DEPB NUMBER(9)
NAME ...
SURNAME ...
....
DEPA
------
ID NUMBER(9)
COLA1 ......
COLA2 ......
.......
DEPB
------
ID NUMBER(9)
COLB1 .......
COLB2 .......
.....
PERSONEL is in this case the child table of DEPA AND DEPB.
but it can only be the child table of one parent table at a time.
so either FK_DEPA or FK_DEPB must have a value but neither can these 2 columns be null nor can they have a value at the same time.
we have to declare both FK fields as nullable, but that's not entirely true, because one of these columns must always have a value.
is there a way of declaring such a constraint/relationship in an RDBMS (such as Oracle) ?
or is there a better solution to this problem (like using only one FK field, if possible) ?
Note : I also posted this on the Oracle forum since we use Oracle and we wondered whether there is also an Oracle specific solution or not.
|
|
04-09-10, 06:56
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
yeah, it's called a CHECK constraint
Code:
CREATE TABLE personel
( id ...
, fk_depa ...
, fk_depb ...
, CHECK ( fk_depa IS NULL AND fk_depb IS NOT NULL
OR fk_depa IS NOT NULL AND fk_depb IS NULL )
);
no idea if oracle supports this, and by the way, please don't post the same question in more than one forum
|
|
04-09-10, 06:58
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
|
|
Grrr - damned forum blew up on me.
Without more information I couldn't say for sure but my gut is I don't like the design.
However, this is easy to do:
Implementing Table Interfaces - SQLTeam.com
The above uses SQL Server syntax but it will be possible to add the same in Oracle using similar (possibly identical) syntax.
Thanks for letting us know re the other thread. I am going to lock it and link to here - we don't allow cross posting. If we fail you here then you can repost in the Oracle forum once this thread is written off.
|
|
04-09-10, 07:10
|
|
Registered User
|
|
Join Date: Oct 2002
Posts: 35
|
|
thanks for the quick reply.
ok, I won't cross post if it's forbidden. no prob.
|
|
04-09-10, 07:39
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Don't worry about it. I suppose forbidden sounds a bit strong. It is simply that sometimes you get people putting in effort on questions that have already been covered off in another thread. AS such, we like to only have one live thread at a time. You did the right thing by mentioning the two threads.
|
|
04-09-10, 09:50
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,409
|
|
>PERSONEL is in this case the child table of DEPA AND DEPB.
Design flaw!
Only single DEPARTMENT table should exist with column for DEPT_NAME; which contains DEPA, DEPB, etc.
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
|
|
04-09-10, 09:59
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Quote:
Originally Posted by anacedent
Design flaw!
Only single DEPARTMENT table should exist with column for DEPT_NAME; which contains DEPA, DEPB, etc.
|
It would seem so but the problem appears to have been abstracted and simplified so much that we cannot say this definitively.
|
|
04-09-10, 10:52
|
|
Registered User
|
|
Join Date: Oct 2002
Posts: 35
|
|
Quote:
Originally Posted by pootle flump
It would seem so but the problem appears to have been abstracted and simplified so much that we cannot say this definitively.
|
Exactly. 
The other way it would be too complicated to explain and my English knowledge isn't that good.
|
|
04-09-10, 11:05
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Sure.
I only differ from Anacedent though in that I am not prepared to say it is definitely a design flaw, only that it looks like one! If you are confident it is the correct design and are confident in your modelling skills then it is your database to do with as you like
|
|
04-09-10, 11:20
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,409
|
|
The logic becomes untenable when tens or hundreds of different departments exist!
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
|
|
04-09-10, 11:34
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Assuming he has a table per department then yes. But again you are inferring a lot from an abstracted problem.
|
|
04-09-10, 12:05
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 1,072
|
|
If you cannot get the constraint to work, then you can implement the same logic, with a tailored error message in a table trigger
Code:
create or replace trigger personnel_bt
before insert or update on personnel
for each row
begin
if (:NEW.FK_DEPA is null and :NEW.FK_DEPB is not null)
or
(:NEW.FK_DEPA is not null and :NEW.FK_DEPB is null) then
raise_application_error(-20500, 'Each employee can only belong to one department.');
end if;
end personnel_bt;
--=cf
|
|
04-09-10, 16:21
|
|
Registered User
|
|
Join Date: Dec 2007
Location: London, UK
Posts: 732
|
|
Quote:
Originally Posted by msegmx
is there a way of declaring such a constraint/relationship in an RDBMS (such as Oracle) ?
|
Create a supertype table representing both DEPA and DEPB. Then reference that supertype table with a single foreign from PERSONEL. Make the foreign key non-nullable.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Two foreign keys, one must have a value the other not
