-
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.
-
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?
-
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.
-
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?
-
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?
-
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.
-
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?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|