OraFAQ Forum: Designer » 'partial' foreign keys ??

Home » Developer & Programmer » Designer » 'partial' foreign keys ??

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

'partial' foreign keys ?? [message #90511]

Sun, 04 January 2004 19:14

woodchuck
Messages: 12
Registered: May 2003

Junior Member

Hello,

My question is, if I have a table with a primary key that is composed of multiple fields, can I have foreign keys to this table that is only a subset of the original primary key set?

Example:

Employee_Table
==============
EMP_ID PK
L_NAME PK
F_NAME PK
B_DATE PK
AGE
SALARY

Employee_Department_Table
=========================
DEPT_ID PK
EMP_ID FK

Can I do the above? Will Oracle force me to include the L_NAME, F_NAME, B_DATE fields as foreign keys in the Employee_Department_Table because the primary key in Employee_Table is the set of those four fields?

Thanks in advance,
woodchuck

Report message to a moderator

Re: 'partial' foreign keys ?? [message #90512 is a reply to message #90511]

Sun, 04 January 2004 22:44

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Hi,

Unfortunately it is not possible to reference a partial primary key. Look at this example:

SQL> create table t1 (
  2          c1 number,
  3          c2 varchar2(30),
  4          constraint t1_pk primary key (c1,c2));
Table created.

SQL> create table t2 (
  2          c1 number,
  3          c3 varchar2(50),
  4          constraint t1_fk foreign key (c1) references t1);
        constraint t1_fk foreign key (c1) references t1)
ERROR at line 4:
ORA-02256: number of referencing columns must match referenced columns

One possible solution to your problem is to make Employee_Table.EMP_ID the PK, and create an additional UNIQUE CONSTRAINT on (EMP_ID, L_NAME, F_NAME, B_DATE).

Best regards.

Frank

Report message to a moderator

Re: 'partial' foreign keys ?? [message #90513 is a reply to message #90512]

Mon, 05 January 2004 03:12

woodchuck
Messages: 12
Registered: May 2003

Junior Member

Hello, thanks for your reply.

I like your suggestion a lot. But what are the implications for doing so? Is there any harm or negative side-effects for using UNIQUE CONSTRAINTs in place of PKs?

Thanks in advance,
woodchuck

Report message to a moderator

Re: 'partial' foreign keys ?? [message #90523 is a reply to message #90513]

Sun, 18 January 2004 08:34

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

As a rule, every table should have a primary key. There is no harm in also having additional unique keys - in fact from a design point of view these are mandatory if there are additional rules regarding uniqueness and you want to enforce them (otherwise why have the rules?) Foreign keys on other tables can refer to either the PK or a UK on the parent table.

The reason why you can't use just part of a key is that each record in the "child" table must be able to point to exactly one "parent" record. If ten employees had the same EMP_ID (which is possible in the original design) then which is the parent - for example, what should happen if one of them is deleted? What value would you display against each DEPT_ID in a report?

Also if EMP_ID is now unique (due to the PK), then the combination EMP_ID + L_NAME + F_NAME + B_DATE will be unique regardless of the values of L_NAME, F_NAME or B_DATE. Perhaps the rule should be that L_NAME + F_NAME + B_DATE should be unique on their own, i.e. you can not have two employees with the same first and last name born on the same day?

Also appending "_TABLE" to the name of every table seems a bit redundant. I would personally go for "EMPLOYEES" and "EMPLOYEE_DEPARTMENTS".

Report message to a moderator

Previous Topic:	Database Design Problem
Next Topic:	Oracle 9i Designer - Journal Tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 04:04:54 CST 2024