RE: Surrogate Keys

you have 3 options:
1. include master pk in child pk
2. don't include master pk in child pk and set a not null constraint in child table for master fk
3. don't include master pk in child pk and set a null constraint in child table for master fk

indices will be larger in the first case ( pk for 2 columns and a fk index
).

First option is for a dependent entity.

HTH,
Michael Netrusov

-----Original Message-----
From: kirschw_at_hoffman.army.mil [mailto:kirschw_at_hoffman.army.mil] Sent: Monday, July 17, 2000 16:23
To: Multiple recipients of list ORACLE-L Subject: Surrogate Keys

I'm in the mud over implementing surrogate keys in master-child tables. For example,

table master (

   master_sug_key number primary key -- generated by nextval , datam char
)

table child (
  child_sug_key number primary key -- generated by nextval , master_sug_key number foreign key not null , datac char
)

If I then use ERWin to draw the E-R diagram, I get two independent entities, unless I also include the
element "master_sug_key" as part of the primary key in the child table, Should I, should I not, or doesn't it matter? Am I missing something basic here? The indexes will be fewer if I do include "master_sug_key" in child's primary key. Any pointers gratefully accepted.

-- 
Author: 
  INET: kirschw_at_hoffman.army.mil

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
also send the HELP command for other information (like subscribing).