Home » Developer & Programmer » Designer » 'partial' foreign keys ??
'partial' foreign keys ?? [message #90511] Sun, 04 January 2004 19:14 Go to next message
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
Re: 'partial' foreign keys ?? [message #90512 is a reply to message #90511] Sun, 04 January 2004 22:44 Go to previous messageGo to next message
Frank Naude
Messages: 4565
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
Re: 'partial' foreign keys ?? [message #90513 is a reply to message #90512] Mon, 05 January 2004 03:12 Go to previous messageGo to next message
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
Re: 'partial' foreign keys ?? [message #90523 is a reply to message #90513] Sun, 18 January 2004 08:34 Go to previous message
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".
Previous Topic: Database Design Problem
Next Topic: Oracle 9i Designer - Journal Tables
Goto Forum:
  


Current Time: Mon Sep 28 10:39:55 CDT 2020