primary key - concatenated or autonumber?

[Susan H.]

I have a parent (tblYouth) and child (tblAddress) tables with one-to-many relationship. I need help setting the correct primary key for tblAddress.

tblYouth
PK YouthID

tblAddress
PK? AddressID (autonumber)
Conc PK? YouthID
Conc PK? AddressBeginDate

Sample Data:
AddressID Youth ID AddressBeginDate AddressEndDate Address
1 5 1/1/2013 3/4/2013 527 Main St.
2 5 3/5/2013 Null 237 Elm St.
3 6 1/1/2013 Null 422 Ash Road

A youth will have many addresses, but the youth cannot have a duplicate AddressBeginDate. More than one youth may have the same AddressBeginDate. The YouthID + AddressBeginDate fields uniquely identify the record. The AddressID also uniquely identifies the record, but I'm wondering if I have to use the concatenated primary key because there can be multiple YouthIDs and multiple AddressBeginDates, but there can't be multiple YouthID + AddressBeginDate. Is using the concatenated fields as the primary key the only way to keep those combined fields unique?

I'll be displaying and updating the latest address record for each youth ona subform, and I'll be using another subform to add records to the tblAddress. Comboboxes will be involved.

Thanks!

 

[(PeteCresswell)]

Per Susan H.:

I have a parent (tblYouth) and child (tblAddress) tables with one-to-many relationship. I need help setting the correct primary key for tblAddress.

tblYouth
PK YouthID

tblAddress...

I wouldn't even think twice: Autonumber PK or maybe, if circumstances
warrant, manually-incremented PK.

Every time I've tried using a PK with any intelligence built into it
(SSN, whatever), I've lived to regret it.

For my money, a PK should always be a blind, dumb number.

 

[susanhull]

I have a parent (tblYouth) and child (tblAddress) tables with one-to-manyrelationship. I need help setting the correct primary key for tblAddress. tblYouth PK YouthID tblAddress PK? AddressID (autonumber) Conc PK? YouthID Conc PK? AddressBeginDate Sample Data: AddressID Youth ID AddressBeginDate AddressEndDate Address 1 5 1/1/2013 3/4/2013 527 Main St. 2 5 3/5/2013 Null237 Elm St. 3 6 1/1/2013 Null 422 Ash Road A youth will have many addresses, but the youth cannot have a duplicate AddressBeginDate. More than one youth may have the same AddressBeginDate. The YouthID + AddressBeginDate fields uniquely identify the record. The AddressID also uniquely identifies therecord, but I'm wondering if I have to use the concatenated primary key because there can be multiple YouthIDs and multiple AddressBeginDates, but there can't be multiple YouthID + AddressBeginDate. Is using the concatenatedfields as the primary key the only way to keep those combined fields unique? I'll be displaying and updating the latest address record for each youthon a subform, and I'll be using another subform to add records to the tblAddress. Comboboxes will be involved. Thanks!

I'm a fan of simplicity, so I agree with you Pete. And thanks for the comment. So how do I keep users from a entering a duplicate AddressBeginDate forthe same youth? How do I keep the YouthID + AddressBeginDate field combinations unique?

 

[susanhull]

I have a parent (tblYouth) and child (tblAddress) tables with one-to-manyrelationship. I need help setting the correct primary key for tblAddress. tblYouth PK YouthID tblAddress PK? AddressID (autonumber) Conc PK? YouthID Conc PK? AddressBeginDate Sample Data: AddressID Youth ID AddressBeginDate AddressEndDate Address 1 5 1/1/2013 3/4/2013 527 Main St. 2 5 3/5/2013 Null237 Elm St. 3 6 1/1/2013 Null 422 Ash Road A youth will have many addresses, but the youth cannot have a duplicate AddressBeginDate. More than one youth may have the same AddressBeginDate. The YouthID + AddressBeginDate fields uniquely identify the record. The AddressID also uniquely identifies therecord, but I'm wondering if I have to use the concatenated primary key because there can be multiple YouthIDs and multiple AddressBeginDates, but there can't be multiple YouthID + AddressBeginDate. Is using the concatenatedfields as the primary key the only way to keep those combined fields unique? I'll be displaying and updating the latest address record for each youthon a subform, and I'll be using another subform to add records to the tblAddress. Comboboxes will be involved. Thanks!

For others who may have this same issue, I found a primary key/index post that answers my questions. I'm using the AddressID autonumber as my primary key, and creating a compound index on the YouthID and AddressBeginDate fields so the two combined fields aren't duplicated.

 

[Douglas J Steele]

No reason to concatenate the fields. Like any other index, a Primary Key can
have up to 9 separate fields in in.

"Susan H." wrote in message

I have a parent (tblYouth) and child (tblAddress) tables with one-to-many
relationship. I need help setting the correct primary key for tblAddress.

tblYouth
PK YouthID

tblAddress
PK? AddressID (autonumber)
Conc PK? YouthID
Conc PK? AddressBeginDate

Sample Data:
AddressID Youth ID AddressBeginDate AddressEndDate Address
1 5 1/1/2013 3/4/2013 527 Main St.
2 5 3/5/2013 Null 237 Elm St.
3 6 1/1/2013 Null 422 Ash Road

A youth will have many addresses, but the youth cannot have a duplicate
AddressBeginDate. More than one youth may have the same AddressBeginDate.
The YouthID + AddressBeginDate fields uniquely identify the record. The
AddressID also uniquely identifies the record, but I'm wondering if I have
to use the concatenated primary key because there can be multiple YouthIDs
and multiple AddressBeginDates, but there can't be multiple YouthID +
AddressBeginDate. Is using the concatenated fields as the primary key the
only way to keep those combined fields unique?

I'll be displaying and updating the latest address record for each youth on
a subform, and I'll be using another subform to add records to the
tblAddress. Comboboxes will be involved.

Thanks!

 

[Phil Hunt]

For primary key, use autonumber.

On tblAddress, put a constraint (unique index) on YouthID+AddressBeginDate
Please note that this index has nothing to do with primary key or foreign
key. Just a constraint on the table


I have a parent (tblYouth) and child (tblAddress) tables with one-to-many
relationship. I need help setting the correct primary key for tblAddress.

tblYouth
PK YouthID

tblAddress
PK? AddressID (autonumber)
Conc PK? YouthID
Conc PK? AddressBeginDate

Sample Data:
AddressID Youth ID AddressBeginDate AddressEndDate Address
1 5 1/1/2013 3/4/2013 527 Main St.
2 5 3/5/2013 Null 237 Elm St.
3 6 1/1/2013 Null 422 Ash Road

A youth will have many addresses, but the youth cannot have a duplicate
AddressBeginDate. More than one youth may have the same AddressBeginDate.
The YouthID + AddressBeginDate fields uniquely identify the record. The
AddressID also uniquely identifies the record, but I'm wondering if I have
to use the concatenated primary key because there can be multiple YouthIDs
and multiple AddressBeginDates, but there can't be multiple YouthID +
AddressBeginDate. Is using the concatenated fields as the primary key the
only way to keep those combined fields unique?

I'll be displaying and updating the latest address record for each youth on
a subform, and I'll be using another subform to add records to the
tblAddress. Comboboxes will be involved.

Thanks!

 

[Access Developer]

... So how do I keep users from a entering a duplicate
AddressBeginDate for the same youth? How do I keep
the YouthID + AddressBeginDate field combinations
unique?

If you specify the YouthID and AddressBeginDate as an Index and specify it
as Unique (setting it as Primary Key forces it to be a Unique Index), Access
will not allow you to enter the same YouthID and AddressBeginDate. (If you
think the error message is unfriendly, do your own error message).

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

in message

I have a parent (tblYouth) and child (tblAddress) tables with one-to-many
relationship. I need help setting the correct primary key for tblAddress.
tblYouth PK YouthID tblAddress PK? AddressID (autonumber) Conc PK? YouthID
Conc PK? AddressBeginDate Sample Data: AddressID Youth ID AddressBeginDate
AddressEndDate Address 1 5 1/1/2013 3/4/2013 527 Main St. 2 5 3/5/2013
Null 237 Elm St. 3 6 1/1/2013 Null 422 Ash Road A youth will have many
addresses, but the youth cannot have a duplicate AddressBeginDate. More
than one youth may have the same AddressBeginDate. The YouthID +
AddressBeginDate fields uniquely identify the record. The AddressID also
uniquely identifies the record, but I'm wondering if I have to use the
concatenated primary key because there can be multiple YouthIDs and
multiple AddressBeginDates, but there can't be multiple YouthID +
AddressBeginDate. Is using the concatenated fields as the primary key the
only way to keep those combined fields unique? I'll be displaying and
updating the latest address record for each youth on a subform, and I'll
be using another subform to add records to the tblAddress. Comboboxes will
be involved. Thanks!

I'm a fan of simplicity, so I agree with you Pete. And thanks for the
comment.