Non-primary keys in relationships (1 Viewer)

[rsf]

I've been wrestling with a design dilemma for a few days and I'd welcome some advice.
I know that it is technically possible in Access to set up a relationship in which a foreign key in one table references a non-primary, but unique, indexed field in another table.
I have a situation where it seems I may need to create tables using such a setup... but I can't help thinking that it seems a bad idea.
Can anyone offer any advice on whether using non-primary (but obviously unique) keys in relationships is generally bad practice or if it is OK? Thanks.

 

[gemma-the-husky]

its no problem - but why would you want to use an index other than the primary key

 

[georgedwilkinson]

IMHO, don't do it. There is something wrong with your design if you think you need to.

 

[rsf]

Thanks for the replies. The reason for using non-primary keys in the relationships is this...
The data is not entered directly into the database, so I can’t rig up data entry forms to enforce data to comply with relationship rules as it is entered.
The data is first entered into Excel and then imported into the database (there are a few reasons for this: it’s mainly due to the fact that much of the data is scientific field data which is entered on PDAs (which do not run Access) in the field; secondly the users wish to keep using Excel to enter data as they are more familiar with it and they like to use Excel features such as autofill, fill handles etc).
In Access, I want to use surrogate, autonumber keys as primary keys because the data in the natural keys in this database could be liable to change.
Unfortunately, due to the data being entered outside of the database, the users will not be able to enter a ‘parent’ record’s autonumber field value as a foreign key in a dataset as the ‘parent’ record may not even exist in the Access database when they are entering the ‘child’ data.
The data in the natural key will, however, be known and entered, so that is why I intend to use the natural key to relate the tables in the database, but I also want to have the surrogate autonumber field to provide a permanent, non-changing identifier for each record – hence that will be the primary key.
If you have any alternative suggestions, I’d be pleased to hear them!

 

[gemma-the-husky]

i do something very similar to what you are doing

but when i import the data, i match on the available data key, and then store the the "true (if you like)" access primary key based on that match. You can do this with an intermediate query, easy enough. or you could add a field to the imported table, and store it there.

 

[dportas]

There is nothing fundamentally wrong with using any candidate key for referential integrity. Both logically and practically speaking all keys are equal and "primary" keys are only as "special" as you want them to be.

 

[rsf]

Thanks for taking the time to reply everyone, appreciated...

 

[The_Doc_Man]

As a matter of fact, if that unique indexed but non-PK field is subject to change, you are better off using a synthetic PK (due to nightmarish relational integrity issues when changing PKs). I would agree with gemma that when you import the data, it probably wouldn't hurt to pull in the "real" PK for the relationship.

 

[rsf]

Fair point, Doc Man - although wouldn't setting the relationships to 'cascade updates' prevent any RI issues when/if the PK changed?