Primary vs alternate key
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Primary vs alternate key

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Primary vs alternate key

    We have a table that we acquired from a vendor that has tables with PK of varchar2(1000) - the requirement is to stick to the same table design, so for the new SELECT only tables that we are adding that need to reference to this varchar2(1000) field, I was thinking of just using AK for uniqueness and not making it part of the PK. Any thoughts/suggestions on this ? BTW What is the difference between Primary key & alternate key - apart from alternatekey being able to store NULL. They both are unique & they both have indexes. Is there a difference in the way the Indexes are stored ?

    Thanks,
    Shiva.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, there's no difference in index structure between PK and UKs supporting indexes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The difference is really logical ... the AK cannot be used as a reference for a foreign key, and according to good DB practice the PK values should never change -- the AK values could.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    The difference is really logical ... the AK cannot be used as a reference for a foreign key
    If we are talking about the same things here: (AK = alternate key == UK = unique key), then AK can of course be used as reference for foreign keys...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by jmodic
    If we are talking about the same things here: (AK = alternate key == UK = unique key), then AK can of course be used as reference for foreign keys...
    Are you sure about that?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Yes AK can be used for foreign key reference. I am currently doing that wherever possible as the PK is a varchar2(1000).

    If I need to reference this PK in another table, how about making it an AK in the reference table ? e.g.

    Table1
    A varchar2(1000) - Primary Key

    Table2
    A Varchar2(1000) - references to Table1
    B Number
    A & B Combo is unique in Table2.

    For the above scenario, I hate to make 'A' in table2 as part of the primary key due to its size. Since I need to enforce the uniqueness, can I make it part of the AK or since there really is no difference between AK & PK as far as storing/indexes goes, it does'nt impact whether 'A' is part of the PK or AK in Table2 ?

    In other words, which of the following is a better design :
    Table2
    A Varchar2(1000) - (FK) (PK,1)
    B Number (PK,2)
    OR
    Table2
    A Varchar2(1000) - (FK) (AK,1)
    B Number (AK,2)

    Thanks,
    Shiva.
    Last edited by rshivagami; 03-17-2004 at 10:58 AM.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    Are you sure about that?
    If there wasn't that smiley in your message I might even think that you are serious with that question!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    My bad ... I was testing it with ...
    Code:
    SQL> create table my_tab2 (my_date references my_table(my_date));
    create table my_tab2 (my_date references my_table)
                                             *
    ERROR at line 1:
    ORA-02268: referenced table does not have a primary key
    ... instead of ...
    Code:
    SQL> create table my_tab2 (my_date references my_table(my_date));
    
    Table created.

    Anyhoo, I don't see an advantage in this case in defining an AK instead of a PK -- it sounds like a disadvantage as far as maintainability goes.

    the real problem is that column being the key on table 1, i think, but i guess you can't do much about that.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Anyhow, I do not see any other way of doing this and I cannot really do anything about the PK.
    Thanks for the insight guys.

    Shiva.

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