master detail problem [message #682560] |
Wed, 28 October 2020 17:12 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/51284ef89d03d81360279635761fcedd?s=64&d=mm&r=g) |
compuscience
Messages: 97 Registered: September 2012
|
Member |
|
|
i create master table emp(emp_number,emp_name,id,salary)
with detail 1 dept(dept_name,emp_number,id)
with detail 2 job(job_name,emp_number,id)
i didn't make primay key and forign key i make relation between them with emp_number,id
the only problem when i make update in master block not affect in detail 1 and detail 2 block
what can i do???
|
|
|
|
|
|
|
|
|
|
|
Re: master detail problem [message #682586 is a reply to message #682582] |
Fri, 30 October 2020 04:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You have been caught out by the problem of using a natural key as a primary key. Bad move! Your primary/foreign key relationships need to be based on surrogate keys, not natural keys. You should have studied the use of surrogate keys and natural keys in college (it is a standard topic that all data science courses should cover) but if not better start now. This article is fair start,
https://en.wikipedia.org/wiki/Surrogate_key
and the papers published by Ralph Kimball take the topic further. Look up Slowly Changing Dimensions, too: They are a problem caused by use of natural keys that has made many multi-terabyte multi-million dollar databases worthless.
|
|
|
|