Alternate Primary Keys

[ytorres]

Is there such thing as an alternate primary key for access tables?

My boss insists there is and I have searched to find nothing on this.

What my boss is sugesting is that depending from with form I am reading or entering data that the primary key changes accordingly to allow for creating new records. In some cases it will have a 3-field primary key and in others a 2-field primary key.

To me it doesn't make any sense. Please help if you understood what I tried to say and what we are trying to do. I need to get back to my boss with an answer to this.

Thanks so much,

Y

 

[BNPMike]

There is no such thing as an alternate primary key. You can certainly have alternate keys - as many as you like. The primary one is just the one you decide to call primary. Normally you would choose the easiest eg the shortest.

As regards your boss, the question is probably irrelevant. For any key you must provide complete data, otherwise you corrupt the database so if you are going to add a record it is difficult to see what you might gain by switching keys.

Having said that I can see a situation where you create a record with a real (outside world) key eg CustomerNo, ProductNo, Timestamp, and then use a short key eg an autonumber to access the record in other screens where you perhaps track the progress of the order.

To be relational a table should have at least one key ie one set of fields that uniquely identifies a row. This key may be all of the fields together. Often in practice there are more than one set of fields which will do this, plus very often the organisation or system designer will create additional internal ids for convenience.

 

[Golom]

The database terminology you are (or possibly your boss is) talking about is a "candidate" key. That's any field or combination of fields that uniquely identify a record in a table.

For example, you may have a field called "EmployeeID" that you assign sequentially that uniquely identifies an employee but the Employee's Social Security Number also identifies the employee uniquely.

Both are "candidate" keys and you will select one of them as the primary key. As BNPMike says, there is only one primary key in a table.

The distinction (from an Access point of view) is that the key that you select as primary MUST be unique and may not contain NULLs. Candidate keys are not required to be unique or non-NULL (as far as Access is concerned) although it would probably be a good idea to designate them as such.

Note that regardless of how you are searching for a record in the database (i.e. primary or candidate key); when you add a new record you MUST provide valid primary key information ... just candidate key information is not enough.

 

[ytorres]

Thanks for your reply.

I needed for you knowledgable programmers to help me understand this thing.

The candidate key doesn't apply in this situation.

I have a table called FollowUps with a primary key composed of FollowUp No (code generated), Action No (the number of the corrective or preventive action to which the followup is related, as 1 action may have more than one follow up) and InvestigationNo (which is the daddy of both action and followup, meaning that an Investigation has 1 or more actions related to it and each of those actions has 1 or more followups related to it).

Now, as it turns out, the db must provide for an Investigation to have a followup directly related to it. No action has to be related to the investigation to create that follow up as it will not relate to the action but to the Investigation.(messy explanation, I know! Sorry!!)

So my initial idea was that I needed a new table because, even when the data and fields are practically the same as in the FollowUps table, the primary key needs only 2 fields (InvestigationNo and FollowUpNo, as an Investigation may have more than one followup) and not three.

That's when my boss sugested the use of alternate primary keys depending on what type of followup I was creating.

I apologized if I didn't explain too well.

Thanks for your help. I am most certain that I need the new table, right?

 

[Golom]

The direct "Investigation/FollowUp" table is one possibility. Another possibility is to place another field in the "Action" table called (for example) "ActionType". That field would be either "A" for "Action" or "F" for "Followup Only" (I'm just making this up as I go along obviously.)

When a Followup directly related to an Investigation is created then a new record is generated in the "Action" table with type "F". You can then find all Followups related to Actions with
[tt]
WHERE Action.ActionType = "A"
[/tt]
Followups related directly to investigations with
[tt]
WHERE Action.ActionType = "F"
[/tt]
and you can find all followups of both types by omitting the "Action.ActionType" condition check.

This approach allows you to keep all followups in one table and eliminates the need to fool with messy outer joins when retrieving followups from different tables.