Connecting Tech Pros Worldwide Forums | Help | Site Map

How to create unique primary key?

Member
 
Join Date: Mar 2008
Location: Indianapolis,IN
Posts: 68
#1: Jun 25 '09
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!!

Member
 
Join Date: Aug 2007
Posts: 57
#2: Jun 26 '09

re: How to create unique primary key?


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.
ADezii's Avatar
Expert
 
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,219
#3: Jun 26 '09

re: How to create unique primary key?


Quote:

Originally Posted by jinalpatel View Post

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!!

  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.
Site Addict
 
Join Date: Mar 2007
Location: Oakland, California, USA
Posts: 522
#4: Jun 26 '09

re: How to create unique primary key?


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
Quote:
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?
mshmyob's Avatar
Expert
 
Join Date: Jan 2008
Location: witness protection
Posts: 618
#5: Jun 26 '09

re: How to create unique primary key?


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,
ADezii's Avatar
Expert
 
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,219
#6: Jun 26 '09

re: How to create unique primary key?


Quote:

Originally Posted by mshmyob View Post

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,

Makes perfect sense to me, mshmyob. Let's wait and see what the OP is really looking for.
Member
 
Join Date: Mar 2008
Location: Indianapolis,IN
Posts: 68
#7: Jul 6 '09

re: How to create unique primary key?


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!!
Member
 
Join Date: Mar 2008
Location: Indianapolis,IN
Posts: 68
#8: Jul 6 '09

re: How to create unique primary key?


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.
Expert
 
Join Date: Jul 2008
Location: Maryland
Posts: 1,176
#9: Jul 6 '09

re: How to create unique primary key?


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.
mshmyob's Avatar
Expert
 
Join Date: Jan 2008
Location: witness protection
Posts: 618
#10: Jul 6 '09

re: How to create unique primary key?


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,

Quote:

Originally Posted by jinalpatel View Post

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!!

Reply


Similar Microsoft Access / VBA bytes