Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006

    Unanswered: foreign key which references primary key of same table (was "Dbms")

    WHat purpose does it serve in a situation where there is a foreign in a table which references the primary key of the same table?
    for example:
    Employee(Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, SuperSSN,
    In Employee: SuperSSN is a foreign key that references Employee(SSN); Dno is a
    foreign key that references Department(Dnumber).??

  2. #2
    Join Date
    Nov 2003
    I have been told that thisis poor data structure. Even with that said I believe that a foreign key to a primary key within the same table will make sure that there is a parent data before child data can be entered.
    Hope this helps.

  3. #3
    Join Date
    Sep 2002
    Provided Answers: 1
    The purpose is to create a relationship between one employee and another - in this case "employee SSN1 is supervised (or managed) by employee SSN2".

    The standard Oracle "scott.emp" table has this:
    SQL> select empno, ename, mgr
      2  from emp;
         EMPNO ENAME             MGR
    ---------- ---------- ----------
          7839 KING
          7698 BLAKE            7839
          7782 CLARK            7839
          7566 JONES            7839
          7788 SCOTT            7566
          7902 FORD             7566
          7369 SMITH            7902
          7499 ALLEN            7698
          7521 WARD             7698
          7654 MARTIN           7698
          7844 TURNER           7698
          7876 ADAMS            7788
          7900 JAMES            7698
          7934 MILLER           7782
    14 rows selected.
    SQL> select e.ename || ' is managed by ' || nvl(m.ename, 'nobody')
      2  from emp e
      3  left outer join emp m
      4  on e.mgr = m.empno;
    JONES is managed by KING
    CLARK is managed by KING
    BLAKE is managed by KING
    JAMES is managed by BLAKE
    TURNER is managed by BLAKE
    MARTIN is managed by BLAKE
    WARD is managed by BLAKE
    ALLEN is managed by BLAKE
    MILLER is managed by CLARK
    FORD is managed by JONES
    SCOTT is managed by JONES
    ADAMS is managed by SCOTT
    SMITH is managed by FORD
    KING is managed by nobody
    14 rows selected.
    There is also a special kind of query you can write with hierarchies like this one:
    SQL> select rpad('...',(level-1)*3,'...')||ename
      2  from emp
      3  start with ename='KING'
      4  connect by prior empno = mgr;
    14 rows selected.
    PS if you post another question, please try to give a more meaningful title than "Dbms". Every question in this forum is about a DBMS. The purpose of the thread title is to give people an idea of what the question is about specifically!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts