Re: Primary -- foreign keys

Mike Harris wrote:  

> For emample, in a "personnel" table you might want to record the id of
> each person's manager --- which would be the same as the employee id.

Not doing it like that, you wouldn't. Note the structure is not like this:

create table abc (
  x integer primary key,
  y char(20),
  m integer references abc(x)
)

where x represents the employee ID and m represents the employee ID of the manager. Rather, it was this:

create table abc (

    x integer primary key,
    y char(20),
    foreign key (x) references abc(x)
)

Where the SAME COLUMN represents both the employee ID of the employee and of the manager. In this case, the only possibility is for the employee to manage himself/herself. In this particular case, the foreign key is completely useless, and probably also completely meaningless.

To answer Keith's original question, having a foreign key which references the primary key in the same table is useful in any tree-like hierarchy, such as Mike's employee-manager example. But not the same column in the same table.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Sat Aug 11 2001 - 00:05:15 CEST