Re: Primary -- foreign keys

keith wrote:
>
> Hi
>
> I am trying to understand why would anyone want to create the
> following:
>
> create table abc
> (
> x integer primary key,
> y char(20),
> foreign key (x) references abc(x)
> );
>
> What's being accomplished by creating a forein key which references a
> primary key of the same table??? Primary and foreign keys are on the
> same column of the same table????
>
Hi,
in your example the foreign key constraint is trivial, which makes it diffcult
to explain.

In the example below you can see one primary key constraint, meaning that
any insertion of a tuple into the employee table must have a value for 'name' that does not previously exist in the table (a UNIQUE constraint) and also that you must supply a value (a NOT NULL constraint). Also the foreign key for department tells us that the value we supply for
'department' must exist in the column 'department' of the table 'department'.
Likewise we must supply a value for 'manager' that already exist in the 'name' column of the 'employee' table.

In short it means that each employee must be unique, that he/she must have
a manager that is already an employee and that he/she must be employed to work at an existing department.

But self references for foreign keys have no practical use.

create table employee
  (
    name varchar(30,2),
    salary integer,
    manager varchar(30,2),
    department varchar(20,2),

    primary key (name) ,
    foreign key (department) references department(department),
    foreign key (manager) references employee(name)