Connecting Tech Pros Worldwide Help | Site Map

How to create unique primary key?

  #1  
Old June 25th, 2009, 04:21 PM
Member
 
Join Date: Mar 2008
Location: Indianapolis,IN
Posts: 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!!
  #2  
Old June 26th, 2009, 11:29 AM
Member
 
Join Date: Aug 2007
Posts: 57

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.
  #3  
Old June 26th, 2009, 12:11 PM
ADezii's Avatar
Expert
 
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 20

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.
  #4  
Old June 26th, 2009, 02:28 PM
Site Addict
 
Join Date: Mar 2007
Location: Oakland, California, USA
Posts: 518
Provided Answers: 1

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?
  #5  
Old June 26th, 2009, 02:39 PM
mshmyob's Avatar
Expert
 
Join Date: Jan 2008
Location: witness protection
Posts: 610
Provided Answers: 2

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,
  #6  
Old June 26th, 2009, 04:44 PM
ADezii's Avatar
Expert
 
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 20

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.
  #7  
Old July 6th, 2009, 05:15 PM
Member
 
Join Date: Mar 2008
Location: Indianapolis,IN
Posts: 68

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!!
  #8  
Old July 6th, 2009, 05:51 PM
Member
 
Join Date: Mar 2008
Location: Indianapolis,IN
Posts: 68

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.
  #9  
Old July 6th, 2009, 06:00 PM
Lives Here
 
Join Date: Jul 2008
Location: Maryland
Posts: 1,121
Provided Answers: 18

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.
  #10  
Old July 6th, 2009, 06:02 PM
mshmyob's Avatar
Expert
 
Join Date: Jan 2008
Location: witness protection
Posts: 610
Provided Answers: 2

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 Threads
Thread Thread Starter Forum Replies Last Post
Can I create unique index on selected records ? Alvin SIU answers 6 August 6th, 2008 10:25 PM
Create Primary Key on View js answers 1 June 27th, 2008 06:20 PM
Primary key Alexandr answers 4 July 23rd, 2005 09:59 AM
Create Primary Key on View js answers 1 July 19th, 2005 10:12 PM