Multiple Primary Keys?

[dragonfly]

Hi

I have tried searching the forums, but couldn't locate any relevant information. I have an assignment at the moment and am meant to have 8 tables in my data base, two of which are 'User' and 'Hardware' which have the following fields:

USER:
UserCode (Primary Key)
UserFirstName
UserLastName
DepartmentCode

HARDWARE:
HardwareCode (Primary Key)
HardwareName
VendorCode
PurchaseDate

Now I am meant to have another table called 'Hardware Config' consisting of the Primary Key fields from the two tables above, and they are both meant to be Primary Keys in this table.

I have been able to make the 'Hardware Config' table, but I don't know how to make more than one Primary Key... any ideas?

Thanks in advance - dragonfly

 

[RuralGuy]

I'm not sure *why* you need these two fields as your primary key but you can combine more than one field as the primary key by going to view>indexes while you have the table open in design mode and add another FieldName to the current index.

For clarification, by definition, there can *only* be one primary key.

 

[RV]

Now I am meant to have another table called 'Hardware Config' consisting of the Primary Key fields from the two tables above, and they are both meant to be Primary Keys in this table.

If you intend to set up your Config table as a junction table between the USER and the HARDWARE table, then I doubt whether you're heading in the right direction.
Do yourself a favor and tell us what it is you're after.

RV

 

[Pat Hartman]

Hardware generally belongs to a single individual and that would dictate a 1-many relationship with the UserID being stored in the hardware table. However, if you have a requirement to track history for this relationship, then you need a Many-Many relationship and that means a junction table. Some fields might be:

UserCode (pk fld1)
HardwareCode (pk fld2)
StartDate
EndDate
...

To make a multi-field primary key, open the table in design view and click on the first of the several fields to highlight it. Then while holding down the cntl key, click on up to 9 additional fields.

Making a multi-field index is a little more complicated since you have to do it by opening the indexes dialog. With a multi-field index (you'll see the pk defined in this dialog also), only the first line has a name. So if you have two indexes and one has 3 columns and the other has 2, they will look like (dots for spacing)
index1 fldA
....... fldB
....... fldC
index2 fldX
........ fldM

 

[dragonfly]

Thanks, it sounds like I need to make it a Junction table, rather than what I was doing. I honestly don't know what I need as this is part of an assignment for uni and the specifications are rather bare, they just had a list of tables and their field names with primary keys underlined. In the case of the Hardware Config table, the two fields listed were both underlined, and as I was under the impression that you could only have one primary key (hense the 'primary' name) I was getting extremely confused.

EDIT: Thanks for the help guys, I have made the tables I needed, the same way as I was originally trying to. For some reason it just wasn't allowing me to say my changes the other night, who knows what I did. lol. Time to move on to the next road block

 

[keirnus]

Hardware generally belongs to a single individual and that would dictate a 1-many relationship with the UserID being stored in the hardware table. However, if you have a requirement to track history for this relationship, then you need a Many-Many relationship and that means a junction table. Some fields might be:

UserCode (pk fld1)
HardwareCode (pk fld2)
StartDate
EndDate
...

To make a multi-field primary key, open the table in design view and click on the first of the several fields to highlight it. Then while holding down the cntl key, click on up to 9 additional fields.

Making a multi-field index is a little more complicated since you have to do it by opening the indexes dialog. With a multi-field index (you'll see the pk defined in this dialog also), only the first line has a name. So if you have two indexes and one has 3 columns and the other has 2, they will look like (dots for spacing)
index1 fldA
....... fldB
....... fldC
index2 fldX
........ fldM

it took me some time to figure it out...
the catch here is "only the first line has a name" making the rest(blank) a PrimaryKey as well...
thus, having 2 or more primary keys...yey!

a post made almost 3 yrs ago is a post still brand new...

this really helped me a lot!
thanks Pat Hartman and dragonfly for this topic...

 

[andryn]

Easy when you know how.
Thanks guys, it helped me too, four years later!
Isn't the web great.

 

[gemma-the-husky]

andryn

multiple primary keys is a slight misnomer really

there is only one primary key. the others are unique keys, which work the same way. I think the key designated as the primary key affects the physical storage of the table. You dont have ot have a primary key,. but some operations will not work correctly without one.

 

[Laceemae83]

Easy when you know how.
Thanks guys, it helped me too, four years later!
Isn't the web great.

Hey can you help me dumb this down since you are the most recent post can you tell me what you did to get this done- this is the first time I have every done access and its an online class and I am just so lost!!! the instructions my teacher gave was just hold down the shift key but that is NOT working!!! thanks!

 

[RuralGuy]

Is this link helpful? http://www.btabdevelopment.com/ts/mfi

 

[boblarson]

Hey can you help me dumb this down since you are the most recent post can you tell me what you did to get this done- this is the first time I have every done access and its an online class and I am just so lost!!! the instructions my teacher gave was just hold down the shift key but that is NOT working!!! thanks!

Since you have been assigned this by an instructor, we can work this as is, but just note that composite keys (as this is called) are really a pain to use compared to just using a single autonumber for the Primary Key. If you need to limit the input based on several fields (no duplicates say for Date and Name you could us an autonumber primary key and still put a multi-field index at no duplicates and it would keep the rule for you.

To select the fields to be included in the composite key you just do as the instructor said is to hold your shift key down as you click on the little gray squares just next to the field name in the table (while the table is in design view). If the fields you are choosing are not contiguous (meaning next to each other) you can hold your CTRL key down while selecting them. Then after selecting them you click the icon on the toolbar with the picture of a key.

 

[Dayzed]

Since you have been assigned this by an instructor, we can work this as is, but just note that composite keys (as this is called) are really a pain to use compared to just using a single autonumber for the Primary Key.

If you are still around may I ask a question - I'm also taking a database class and had a very similar exercise. We had to make a junction table to relate two many-to-many tables. (Sorry if I'm using the lingo all wrong I'm very much a noob.) So if you say that composite keys are all a pain then what would be the appropriate way of doing this? My instructor seemed to be saying that this was the only correct way to handle this situation. But I'm open to other input from the real world. I don't understand what you mean by "single autonumber for the Primary Key." Does it mean that you would create a NEW primary key for the junction table and have the other two fields as foreign keys? Or am I all mixed up? Thanks!!

 

[boblarson]

If you are still around may I ask a question - I'm also taking a database class and had a very similar exercise. We had to make a junction table to relate two many-to-many tables. (Sorry if I'm using the lingo all wrong I'm very much a noob.) So if you say that composite keys are all a pain then what would be the appropriate way of doing this? My instructor seemed to be saying that this was the only correct way to handle this situation. But I'm open to other input from the real world. I don't understand what you mean by "single autonumber for the Primary Key." Does it mean that you would create a NEW primary key for the junction table and have the other two fields as foreign keys? Or am I all mixed up? Thanks!!

A junction table is different from a normal table. A junction table usually doesn't have any of its keys stored in other tables so a composite key is fine. However, I have had occasion where I did need to store the key from a junction table into another table as a foreign key and so I usually choose to use a surrogate key with the junction table and put a multi-field index on the part which would have been composite.

So your instructor is right in saying that you can use a composite key in this situation. They are wrong, at least in my mind, that it is the only correct way to handle it. When you get out into the real world, sometimes real world scenarios don't come together as neatly as coursework does and depending upon the instructor they may not have run into the situation.

 

[Dayzed]

Thanks for the speedy response! Yes that does make more sense now. That's why I like to talk to people who actually do this stuff and not just teach it. There are so many more situations in the world than show up in the textbooks. Thanks again!