Multiple PK's

Question

If you use two primary keys in one table, will the db use them togethor to make sure the data is not duplicated? Say, if there is an autonumber pk and it assigns the number 1 to a record, and another pk number entered by the user, say 100. Will it not allow any other record with the pk's of 1 and 100 in the db. Or will it not allow another record with either 1 or 100 in either field?

Answer 1

The values of the fields are treated as one value meaning that once a particular combination appears, it cannot reappear when dealing with PK's. I've never used a composite PK with an autonumber, but I would expect that the autonumber field will increment normally. If you are using an Autonumber, that should eliminate the need for a composite PK as the Autonumber is guaranteed to be unique to begin with.

---

David H

Answer 2

Well, the table will keep track of oil wells. There may be more than one well with the same number, but they will be on different locations. So I used the autonumber pk to keep track of each well, but when playing around with the data it will let me enter the same well number twice at the same location. So I was thinking of using the autonumber pk and the well number pk togethor. But, you gave me another idea, I could use the lease name with the well number as well and do away with the autonumber field. Which do you think would be the most efficient for the db?

Answer 3

A table can have one and only one Primary Key, so your question as phrased is a bit confusing!

What you can have is a Primary Key which is comprised of two (or ten, for that matter) fields. There's only one PK though. In your case, you could make the LeaseName and the WellNumber a joint two-field key; this would allow many wells for a given LeaseName, and many different "well #1" provided they were on different leases. This isn't an ideal situation since it assumes that all the leases have different names (and if these are location names or people's names that may not be the case); in addition it would require that all tables related to the wells table must have the same two fields for the link, which can get cumbersome.

One compromise is to use an Autonumber primary key (and Long Integer foreign keys in related tables), and add a unique Index on the combination of LeaseName and WellNumber. To do so open the Indexes tool from the toolbar; put a distinctive name in the left column of the little toolbox (UniqueWellNo let's say); put one of the fields (LeaseName maybe) in the right column; and the second field in the next row. Check the "Unique" checkbox. You'll then be able to add multiple well numbers per lease or multiple leases with the same number, but records that duplicate both will be rejected.

 

---

John W. Vinson/MVP

Answer 4

Thanks again, John. :)

Answer 5

Typically if there is a real-world value the uniquely identifies the record (Social Security number, employee number, contract number, model number, etc.) that's usually a prime choice as a primary key. In fact, there are only a few situations where that wouldn't work as a PK.

---

David H