Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Schema Design Using Surrogate Key

448221Jun 4 2006 — edited Jun 6 2006
I have 2 tables using surrogate keys and a denormalized d_name column to avoid join when fetching d_name:

create table departments (
pk int primary key,
d_id int unique, /*the real pk*/
d_name varchar2(30)
);
create table employees (
pk int primary key,
e_id int unique, /* the real pk*/
/* e_ pk int references departments(pk)*/
d_id references departments(d_id),
d_name varchar2(30)
);

My problem is should we use d_id as the FK in employees or add and use the surrogate key (pk)? What are the pros and cons of each approach?

I would add triggers to both tables so that the d_name,d_id could be changed in any one of them and values auto populated to the other. If I use d_id as the FK, would the constraint causes error before I update the other table. e.g. I change some departments's d_id to 10, then I would need to change that rows to 10 in employees. But before my trigger change the row, would integrity checking fails?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 4 2006
Added on Jun 4 2006
9 comments
9,995 views