primary key (1 Viewer)

[maxmangion]

when defining tables, i normally set an autonumber as a primary key. However, i am working on a particular table, and i would like that no record will have identical values, in three particular fields

aaa bbb 123
aaa ccc 234
ddd fff 555
aaa bbb 123 <- i want to prevent this since a record with those three values already exists

Shall i set these three fields together as a primary key, or can i leave the autonumber as primary key and prevent such duplication in any manner ?

Thank You

 

[KenHigg]

I think I would set this up as a composite pk and use all three... But an understanding of what the data in the fields represent in real life and of the relationships this table may have with other tables may have a bearing...

 

[Rod D]

To add to Ken's thought.
You can add an index to fields that are not a PK and set them to "No Duplicates".

There will be a small performance overhead in maintaining the index, but for most apps this is not worth considering - you do get the bonus that retrieving and sorting on these fields are quicker...

HTH
Rod

 

[Pat Hartman]

Use an autonumber as your primary key since multi-field pk's can be unwieldy. Then create a unique index on the three-field combination. To do this, open the indexes dialog. Assign a name to the index, choose the first field, and set the sort order. Set primary to No; unique to Yes; and Ignore Nulls to Yes. To add the second and subsequent fields, simply choose the field name. As long as nothing appears in the Index Name field, Access assumes that the field belongs to the previously defined index.

 

[maxmangion]

Thank you very much for your idea, i was aware that using three fields as a primary key was not a wise move, however, i didn't know that tip of leaving the index field blank, for the subsequent fields. This works perfectly.

Eventually when such a duplication takes place i get the 3022 error message. Therefore i am planning to create an error handler, which produces a customised message, with the option to either discard the entry, or give you the opportunity to amend th data. Which property/controls should i use i.e Me.Undo, CancelEvent etc ?, and should i place this in the before update event of the form ?

Thank You very much

 

[KenHigg]

Never tried that Pat, Thanks for the tip...

 

[KenHigg]

Never tried that Pat, Thanks for the tip...