By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,656 Members | 1,918 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,656 IT Pros & Developers. It's quick & easy.

How to create unique primary key?

jinalpatel
P: 68
I want to create a primary key that is combined with several fields

Autonumber+InspectorNo(1 to 5)+CountyNo(1 to 35)+Date

How to create it ? Where to write code for it?
Please help!!
Jun 25 '09 #1
Share this Question
Share on Google+
9 Replies


P: 57
Add another field to your table (such as primarykey and set the datatype to text).

using an update query, update that field:
[ID] & [InspectorNo] & [CountyNo] & [Date]

or if Date is not a field name in the table
[ID] & [InspectorNo] & [CountyNo] & Date()

The ID field in this instance is AutoNumber and is the primary key.
Jun 26 '09 #2

ADezii
Expert 5K+
P: 8,684
@jinalpatel
  1. Open the Table containing the above Fields in Design View.
  2. Select the [Autonumber] Field.
  3. Hold the CTRL Key Down, then select the [InspectorNo], [CountyNo], and [Date] Fields.
  4. All 4 Fields should now be selected.
  5. On the Menu Bar, Click Edit ==> Primary Key.
  6. You have now just created a Composite, Primary Key which means that the combination of ALL 4 Fields must be Unique within the Table.
  7. The following Records would not be a Primary Key violation, since the combination of these Fields is Unique:
    Expand|Select|Wrap|Line Numbers
    1. Autonumber]    [InspectorNo]    [CountyNo]    [Date]
    2.     1                4              51        6/26/2009
    3.     2                3              51        6/26/2009
    4.     3                2              37        6/26/2009
    5.     4                5              37        6/26/2009
    6.  
P.S. - You mentioned writing code in order to create this Key. This would be much more difficult, and I'm not exactly sure why you would need to do this programmatically.
Jun 26 '09 #3

100+
P: 675
Why is such a primary key is needed? Although I understand the answer of how to do this, I don't understand the reason. Any dependent tables would not remain related if a value were changed, perhaps because of an entry error.

With a field of type AutoNum, there is a unique key. ADezii's concern
6. You have now just created a Composite, Primary Key which means that the combination of ALL 4 Fields must be Unique within the Table.
7. The following Records would not be a Primary Key violation, since the combination of these Fields is Unique:
is no concern as a non-unique key cannot be generated here.

Is this better addressed in a discussion about Indexes vs. keys?
Jun 26 '09 #4

mshmyob
Expert 100+
P: 904
I think the problem lies in what the OP is really asking for.

Does he want to create a new table with a primary key value consisting of all the field values he mentioned - answered in 2nd post by DT how to do that.

or

Does the OP want to create a composite primary key with the existing table. - this was answered by Adezii post.

OB's concern about existing table relations is valid if the OP now wishes to change the PK to a composite.

Just as a side note if the OP actually wants a composite PK then the AUTONUMBER field would not be needed to be included if 'Each inspector cannot inspect the same county on the same day'

Just my 2 cents for this discussion.

cheers,
Jun 26 '09 #5

ADezii
Expert 5K+
P: 8,684
@mshmyob
Makes perfect sense to me, mshmyob. Let's wait and see what the OP is really looking for.
Jun 26 '09 #6

jinalpatel
P: 68
OK I am sorry for replying late but I was on vacation. I need this kind of primary key because I am working with pendragon software which syncs with PDA units. It is possible that if two inspectors are inspecting the same nursery at the same time(thats why I am taking "seconds" in consideration), if the nursery farm is more than 300 acres.
I need to create this unique primary key to keep the master records unique.
Thanks everyone for answering and meaning ful discussions!! Thanks much!!
Jul 6 '09 #7

jinalpatel
P: 68
also, pendragon software creates its own tables. I want half the data feeled on pc and other half on PDA. I want something like this. When user enters new records, that unique primary key should be created like autonumber is incrmented by itself when you are entering new record.
Jul 6 '09 #8

Expert 100+
P: 1,287
Use a timestamp as a field that is part of the composite primary key, and insert the current time when the record is created. That will prevent duplicates, while allowing you to merge data. As mshmyob pointed out, the autonumber is not necessary in this case.
Jul 6 '09 #9

mshmyob
Expert 100+
P: 904
A primary key consisting of just INSPECTORID, COUNTYID, and DATE is all that is needed if the following rules apply.

1. Each inspector may inspect many counties.
2. Each county may be inspected by many inspectors,

with the following constraints

1. Each inspector may inspect the same county many times but not on the same date
2. Many inspectors may inspect the same county on the same date


No need for time field or autonumber.

cheers,

@jinalpatel
Jul 6 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.