foreign key syntax
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: foreign key syntax

  1. #1
    Join Date
    Aug 2000
    Posts
    19

    Question

    Just a question about foreign keys:

    Can you have a foreign key consisting of only one column that points to a column that is apart of a composite primary key in another table?

    Likewise, can you have only one column of a composite primary key be created as a foreign key pointing to another non-primary key column in another table?

  2. #2
    Join Date
    May 2001
    Location
    Pune
    Posts
    12
    no, u can't do that.. It gives error..
    check this out...

    create table rtest4
    (
    rollno number,
    deptno number,
    constraint pk_rktest4
    primary key(rollno,deptno)
    );

    // cretes table rtest4


    create table rtest5
    (
    deptno number,
    dname varchar2(10)
    ,
    constraint fk_rtest5
    foreign key (deptno) references
    rtest4(deptno)
    );

    //gives error..

    *
    ERROR at line 8:
    ORA-02270: no matching unique or primary key for this column-list


    Your foreign key will also be a composite foreign key in this case just like primary key.
    This works ...

    create table rtest5
    (
    rno number,
    deptno number,
    dname varchar2(10),
    constraint fk_rtest5
    foreign key (rno,deptno) references
    rtest4(rollno,deptno)
    )
    /

    hope this solves ur query and thanks to u coz it also helped me getting my concepts clear.
    Rohit

    Originally posted by zoey
    Just a question about foreign keys:

    Can you have a foreign key consisting of only one column that points to a column that is apart of a composite primary key in another table?

    Likewise, can you have only one column of a composite primary key be created as a foreign key pointing to another non-primary key column in another table?

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    It doesn't let you do it because it doesn't make sense. This is not how relational databases are built.

    - Chris

Posting Permissions

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


Click Here to Expand Forum to Full Width