473,836 Members | 1,388 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

programatically create primary key

sea
Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names into a form? Maybe have
an invisible field on the form called ID that is linked to the id
field in the table? Will this be ok to do? Any other better
suggestions?

Thank you very much!
Nov 13 '05 #1
7 3283
So, I just ran this on a table of 4484 records, of people's names:

SELECT DISTINCT Left([FirstName],2) AS Expr1, Right([Lastname],2) AS Expr2
FROM tblPersons
ORDER BY Left([FirstName],2), Right([Lastname],2);

and got 2398 back.

That's a lot of duplicates.

I expect you'll have a lot of fun using that as a foreign key too.

Work out what the natural primary key of the table is, if you think there is
one, slap a unique no nulls index on it, then insert an autonumber as the
primary key for Access.

That's what I'd do.
"sea" <se*****@hotmai l.com> wrote in message
news:f8******** *************** ***@posting.goo gle.com...
Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names into a form? Maybe have
an invisible field on the form called ID that is linked to the id
field in the table? Will this be ok to do? Any other better
suggestions?

Thank you very much!

Nov 13 '05 #2
Names are not unique, e.g. it's not uncommon for a father and son to have
the same name and live at the same address.

If you want to create this kind of text-based, you need at least 4
characters form surname, + 4 from first name, inserting (say) the underscore
character where the name is less than 4 chars. Then try it, and if it's
already in use, drop the last 3 chars, and replace with a 3 digit number
(i.e. use leading zeros). You can use DMax() to get the highest number used
so far for the prefix.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sea" <se*****@hotmai l.com> wrote in message
news:f8******** *************** ***@posting.goo gle.com...
Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names into a form? Maybe have
an invisible field on the form called ID that is linked to the id
field in the table? Will this be ok to do? Any other better
suggestions?

Thank you very much!

Nov 13 '05 #3
sea wrote:
Is it a good idea to programatically create a primary key?
Yes, I do that all the time. For numerical primary keys only. To be
honest, except for very rare cases, all my primary keys have migrated to
a non-information field.
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name


Nah, as they say. Maybe you won't need all 456976 unique combinations;
and then again, since the way human names are structured, you won't get
these either. My bet is on "en" "on" and "th" to be in the top 3, for
last names.

Don't. Just don't. I agree, this is a case where the value you base the
PK on will not likely change for the entity (one very big reason for me
not to base the PK on information fields).

What is the linking part of your question? I don't understand that, yet
I feel it is a relevant element.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #4
Primary Keys are a good idea. They allow for referential integrity
when using Foreign Key Relationships. That being said, a Best
Practice for creating Primary Keys is to use numeric (integer) values
that have no specific meaning to the data they are identifying.
MS-Access has the Autonumber data type for just that reason.
This simplifies several things:
• Links between tables: creating Primary Keys using Text fields that
are certain to contain duplicate values is almost never a good idea.
If you have a primary key that is the combination of 1 or more columns
then you will have to replicate ALL of the columns and their data in
EVERY table that you want to create Foreign Key Relationships to.
This creates both space and performance issues. The concept of
"natural primary key" can useful but I always define them as Unique
Indexes.
• IF you find out you have a duplicate and there are relationships
defined you only need to update the Numeric Identifier in the child
records

If you are trying to verify that you have unique people you will need
to use more than just First Name and Last Name. I can see your
proposed solution rapidly growing in scope complexity as you try to
manage this.

se*****@hotmail .com (sea) wrote in message news:<f8******* *************** ****@posting.go ogle.com>...
Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names into a form? Maybe have
an invisible field on the form called ID that is linked to the id
field in the table? Will this be ok to do? Any other better
suggestions?

Thank you very much!

Nov 13 '05 #5
rkc

"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:co******** **@news2.solcon .nl...
sea wrote:
Is it a good idea to programatically create a primary key?


Yes, I do that all the time. For numerical primary keys only. To be
honest, except for very rare cases, all my primary keys have migrated to
a non-information field.


What is the point of generating a non-informational key?

Is autonumber unreliable?


Nov 13 '05 #6
There's no problem with the AutoNumber as a unique identifier. (Well, there
was in A2000, but Microsoft fixed it in one of the earlier JET 4 service
packs.) Most of us use AutoNumbers in every application we create.

There's also no problem in using a natural key. It's a question of style,
but I regularly use a 24-char text field for look up tables (such as
categories or types).

There is also no problem using a semi-informational key (based on the client
name for example). It can be useful if you have a crowded screen, e.g. when
creating staff/volunteer rosters to have a code that you fairly easily
recognise. And it circumvents the issue that if you display a non-bound
column (such as the full name of the employee), it just goes blank if the
person is not in the combo's RowSource (e.g. because you are looking at an
old roster that includes inactive staff that you are not in the list.) There
are also drawbacks to the method, e.g. when someone gets married and changes
name.

So, although people tend to have strong committments to doing things in a
particular way, you are free to choose what suits you and experiment with
the benefits and limitations of each approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:6b******** ********@twiste r.nyroc.rr.com. ..

"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:co******** **@news2.solcon .nl...
sea wrote:
> Is it a good idea to programatically create a primary key?


Yes, I do that all the time. For numerical primary keys only. To be
honest, except for very rare cases, all my primary keys have migrated to
a non-information field.


What is the point of generating a non-informational key?

Is autonumber unreliable?

Nov 13 '05 #7
Chuck Grimsby wrote:
A non-information field? Care to expand on that?


as "informatio nal" to the end user. PK values, index numbers, ordering
stuff, grouping keys are not informational as they do not store what the
user entered.

I have several fields that I use for tracing, like who was the last one
to modify this record, and when. Those are samples of non-information
fields, too.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
3130
by: flkeyman | last post by:
Work in legal office. Trying to create solid designed database structure. This is list of tables w/fields and primary keys. Any comments/advice greatly appreciated. tbl-Defendants CaseNumber (primary key) FirstName MiddleName LastName
3
2218
by: PC Datasheet | last post by:
Using code in MyDateBase I create a new database named MyNewDatabase. Now I need to import (or export) using code in MyDatabase tables in another external database named MyData to MyNewDatabase. Is there a way to do it? What is the code? Thanks, Steve
4
4420
by: I_AM_DON_AND_YOU? | last post by:
There is one more problem I am facing but didn't get the solution. In my Setup Program I am not been able to create 2 things (when the program is intalled on the client machine ) : (1) create shortcut to my program/utility (2) Entry in Windows' Start --> Program Menu. Actually in my VB.Net solution I have two projects (1) MYPROGRAM (2) MYPROGRAM_INSTALLER. MYPROGRAM is a "Windows Application". MYPROGRAM_INSTALLER is a "SetUp Wizard"...
4
12455
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is this: can Access create the document and place it as an OLE object to the relevant table? Any help is greatly appreciated. Ricky
5
1987
by: Ron | last post by:
Hi All, Development stage of setting up a new solution here. I CONSTANTLY have to unhook the relationships I've built via the Relationships tab on Tools, then change the Required element of a few controls on a table from yes to no, make some other adjustments to the attributes of various controls, then copy just the structure (so that the primary key resets to 1) to another name, delete the first table, rename the new table to what it...
1
3448
by: vijay | last post by:
HI ALL, How to create excel chart programatically using C#.net 2005. I have created an excel file by using Gembox.Spreadsheet component.Now i want to add excel Chart to this excel file.But Gembox not support the excel chart.So i am in trouble. I want to create excel chart by using data available on excel file whichever i have created by using Gembox programatically. Is anybody knows the solution. ...
7
6765
by: The Mad Ape | last post by:
Hi I have code to programatically create a label. It works but when I try to call a sub to create a tooltip it does not work. I do not get an error so have no idea what it wrong. Please review my code and tell me how to fix it. Thanks The Mad Ape
3
4237
by: =?Utf-8?B?UGF1bA==?= | last post by:
I need to programatically upload a text file to a web server using the HTTPWebRequest object within .Net 2.0. So far, I have determined that: - I need a HTTP content-type of "multipart/form-data". - I need to use the ContentType.Boundary property. That is about as far as I have gotten.
7
5451
by: sachinkale123 | last post by:
Hi, I am developing one web application where I am adding a job to sql server 2005 scheduler programatically. When I run code i m able to add job to schedular but it does not execute on time. If I create the same job manually, job executes on time. When saw differences between these two jobs I found out that Job created manually has following additional value: When I click on 'Job Referencing a Schedule' i see Reference between Schedule...
0
9672
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10859
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10601
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10260
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6982
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4460
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.