Primary Key Limitation

[SarahO]

I wonder if anyone can help me. I have a table in my database which currently uses 9 fields to make up the primary key. I wanted to add a further 2 fields to this to make 11, however Access will only accept 10. Does anyone know any way around this please?

 

[neileg]

Why on earth would you want such a complex PK? In any event, you can't have more than 10 fields in either a PK or a multi-field index.

 

[SarahO]

I built the DB originally, however had it sent away for normalisation and then the 2 databases were reconciled. The database is to schedule jobs required and many of the jobs contain very similar information hence the requirement for a large PK. I assume I will have to try and work out a different PK that uses 10 fields or less.
Thanks

 

[neileg]

Why don't you just use an autonumber as a PK?

 

[dsigner]

Go back to single PK before it or you falls over. If you cannot add an autonumber PK to the jobs table then use a full date and time as PK. It is very unlikely that two jobs will be created to the second and if you make it unique the second job will have to be later to be accepted.

 

[SarahO]

Thanks I will try both ideas. The database uses an excel macro to add some of the jobs where as others are added manually via forms. The excel macro may mean that several jobs are added at once. I'll have a go anyway.
Cheers
Sarah

 

[The_Doc_Man]

I vote for an autonumber PK and let your other 9 fields be ordinary indexes, of which you can have several.

If you have constraints involving parts of your key set, make the constraining parts a compound key that must be unique. Like, USERNAME + SUBMISSIONTIME + QUEUENAME as a compound index.

You should be aware that the more keys you have as a member of a PK, the less useful the PK gets in making things work smoothly.

Also, the concept of relational databases implies that you would use shorter keys as PKs because otherwise the data in common between parent and child would be wasteful.

 

[WindSailor]

I'll go along with everyone else.
To bad there isn't more information on primary keys, indexes etc. strictly for Access. Here is a link geared toward indexes... if anyone can add to it please do.

http://www.access-programmers.co.uk/forums/showthread.php?t=103298