Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Post Unanswered: Help determining keys

    this may be a pretty noob question..

    does a foreign key always have to be a primary key in a table ?

    if no...

    can that foreign key be appended to the existing primary key... and still just let that one record by the referencing foreign key ?

    example.

    if i got a contact table with the primary key being an appended key of Contact Name and Contact Info.. (so that duplicates will not exist for contacts) but i wanna reference these contacts using a Contact ID which is not part of the primary key (but wanna know if i can make it a foreign key)

    Any help rendered to this problem is much appreciated

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I suppose that the simplest way to find answers to such questions is to simply try it - create those tables and experiment with different combinations of primary and foreign keys. Doing that, you'll learn much more than just reading what someone else says.

  3. #3
    Join Date
    Nov 2011
    Posts
    4
    well the thing is...

    Im in the process of drawing up my ERD... so nowhere close to coding just yet

    so im just wondering from now

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by amresh23 View Post
    does a foreign key always have to be a primary key in a table ?
    no

    a foreign key may reference a primary key or a unique key

    so in your example with the primary key of Contact Name and Contact Info, you can also have a Contact ID but you have to declare it UNIQUE in order to reference it from some other table with a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    4
    ok thanks... that helps alot...

    is the second part to the initial thread possible tho ?

    as is it possible to have a primary key with multiple records and use just one record from this as a foreign key to a next table

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    I do not think that making a small research on this topic would take you long time. Definitely less than waiting for an answer in a forum.

    Anyway, you may always consult Oracle documentation, which is available e.g. online on http://tahiti.oracle.com/
    For 11gR2, the definition (syntactic and semantic) of foreign key constraints is placed here: http://download.oracle.com/docs/cd/E...htm#sthref2065
    Foreign key constraints are subject to the following restrictions:
    ...
    The referenced unique or primary key constraint on the parent table or view must already be defined.
    ...
    (there are also other ones; you should ensure none of them is happening also)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by amresh23 View Post
    as is it possible to have a primary key with multiple records and use just one record from this as a foreign key to a next table
    i'm not completely sure i understand this question but i'm pretty sure the answer is yes if you substitute "column" for "records"

    yes, it is possible to have a composite primary key (consisting of multiple columns) and use just one column from this as a foreign key to another table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by amresh23 View Post
    as is it possible to have a primary key with multiple records and use just one record from this as a foreign key to a next table
    If I understand your requirement correctly, you have master table T1 with composite primary key ( C1, C2 ) and in child table T2 you want to reference it only on a part of that primary key (say only on column C1).

    In that case, it is not possible. In the foreign key definition, there is no notice about "part" - the unique or primary key constraint is stated (and meant to be) in a whole. Just think logically about it - which parent would you pick if there could (and would) be duplicates in the referenced column. The foreign key would make no sense at all, so there would be no reason for stating it.

    Now you have two answers. You may pick any one of them, or just run a small test case - two CREATE TABLE statements should not take you long. In fact, you would know the answer now if you did it in the beginning.

Tags for this Thread

Posting Permissions

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