RE: Datamodeling - Generic Arc Design

Sounds like a job for triggers to me. You probably need to have a column in Table A that indicates which table (B or C) it the row is referencing.

Something like this:
Table A

ID	number
PARENT_TYPE	varchar2(1)	-- This will be 'B' or 'C'

Make a packaged procedure something like this:

/*

• Package to handle Action operations in triggers. * */ create or replace package body tableATrigPkg is /*
  • checkParent checks the given information to see if the appropriate
  • parents exist. This is performing the relational constraint check. */ procedure checkParent(p_ID in TABLE_A.id%type, p_parentType in TABLE_A.parent_type%type) is cursor check_B is select 'X' from TABLE_B where id = p_ID; cursor check_C (vID in number) is select 'X' from TABLE_C where id = p_ID; dummy varchar2(1); begin if (p_parentType = 'B') then open check_B(CID); fetch check_B into dummy; if check_B%notfound then close check_B; raise_application_error(-20111, 'Referenced B does not exist.'); end if; close check_B; elsif (p_parentType = 'C') then open check_C(groupID); fetch check_C into dummy; if check_C%notfound then close check_C; raise_application_error(-20111, 'Referenced C does not exist.'); end if; close check_C; end if; end checkParent;

end tableATrigPkg;
/

And add a trigger like this:

create or replace trigger biur_table_a
before insert or update on table_a
for each row
begin

• This trigger checks to make sure that the appropriate parent exists. -- tableATrigPkg.checkParent(:new.id, :new.parent_type); end biur_action_auth;
  /
  

HTH, Diana

-----Original Message-----
From: Jeff Cox [mailto:jeff.cox_at_ips-sendero.com] Sent: Monday, October 30, 2000 1:30 PM
To: Multiple recipients of list ORACLE-L Subject: Datamodeling - Generic Arc Design

Hello,

I am creating a Table Instance Chart from an E-R diagram and was wondering if there is a "rule of thumb" on how to reference the foreign key for my Arc.

I need to create my foreign key reference for Table A which MUST pull the primary key from EITHER Table B or Table C. This foreign key is also part of the primary key for Table A.

Any help is appreciated.

Thanks!

Jeff Cox
IPS-Sendero
Scottsdale, AZ

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeff Cox
  INET: jeff.cox_at_ips-sendero.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may