# Thread: Primary vs alternate key

1. Senior Member
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. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
No, there's no difference in index structure between PK and UKs supporting indexes.

3. Foreign Script Kiddie
Join Date
Aug 2002
Location
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.

4. Super Moderator
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...

5. Foreign Script Kiddie
Join Date
Aug 2002
Location
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...

6. Senior Member
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 09:58 AM.

7. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally posted by slimdave
If there wasn't that smiley in your message I might even think that you are serious with that question!

8. Foreign Script Kiddie
Join Date
Aug 2002
Location
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```
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.

9. Senior Member
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
•