creating unique keys in Access (1 Viewer)

[saleemMSMS]

is there a way of creating combined unique keys in Access ?

 

[Galaxiom]

In design view of the table select one of the fields then hold down shift while choosing the others. Keep holding down shift and right click the selection. Select primary key for the context menu.

 

[boblarson]

Those are called COMPOSITE keys and I personally avoid them like the plague because I do not want to include more than one field in my queries to identify a row. For every record in the child table you need to store all of the composite key fields (redundant data) and you need to link on all of the fields in each query that you want to be able to link on the key. So, I find them to be a pain in the butt and not all that useful.

If I do want to ensure no duplicates, at the table level, for those fields, I create a MULTI-FIELD INDEX with the No duplicates set.

 

[Galaxiom]

I think composite keys do have a place but each situation should be carefully considered. Two-field composite keys can avoid the need for adding a synthetic key field and hence this completely meaningless redundant data in multiple tables. Two natural fields used as a key is actually storing less redundant data than a synthetic key stored in two tables.

Moreover the storing of the "redundant" natural field in multiple tables can sometimes greatly simplify querying those tables. The synthetic key could require a join to another table to retreive a value that could be obtained directly from one of the natural fields without a join at all. This could be more of a pain and definitely far slower than using natural composite key.

However if either of the fields to be used in the composite is text of more than very few characters I would use the synthetic key because Access works with integers much faster than strings. Three fields as a composite key is really starting to become unwieldy.

If the relationships are set up correctly, the double join is automatically created by the query designer when the tables are added.