Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unanswered: Question abt References

    CREATE TABLE MATERIAL (
    CMP_ID INTEGER NOT NULL,
    MTR_ID INTEGER NOT NULL,
    MTR_DESCRIPTION VARCHAR2(80) NOT NULL,
    MTR_CODE VARCHAR2(1) NOT NULL,
    MTR_TYPE VARCHAR2(10) NOT NULL
    CHECK (MTR_TYPE IN ('CDO', 'RFO')),
    CDO_ID INTEGER NULL,
    CONSTRAINT PK_MTR PRIMARY KEY (MTR_ID),
    CONSTRAINT FK_MTR_COMPANY FOREIGN KEY
    (CMP_ID)
    REFERENCES COMPANY ON DELETE CASCADE,
    CONSTRAINT FK_RFO_CDO FOREIGN KEY
    (CDO_ID)
    REFERENCES MATERIAL ON DELETE CASCADE
    );


    in the above create table query we used referrence to the same table.in which situation we have to use this type of references?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Whenever there is a "one to many" relationship between rows in the same table. Typically this will be a hierarchy, like the Oracle emp table has:

    create table emp
    ( empno number(4) primary key
    , ename varchar2(10)
    , mgr references emp
    , ...
    );
    Code:
    SQL> select empno, ename, mgr from emp
      2  order by empno;
    
         EMPNO ENAME             MGR
    ---------- ---------- ----------
          7369 SMITH            7902
          7499 ALLEN            7698
          7521 WARD             7698
          7566 JONES            7839
          7654 MARTIN           7698
          7698 BLAKE            7839
          7782 CLARK            7839
          7788 SCOTT            7566
          7839 KING
          7844 TURNER           7698
          7876 ADAMS            7788
          7900 JAMES            7698
          7902 FORD             7566
          7934 MILLER           7782
    This shows that employe 7369 (SMITH) is managed by employee 7902 (FORD), who in turn is managed by 7566 (JONES), etc.

Posting Permissions

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