Primary key question

[Anthony]

I am creating a database and I would like to create a unique primary
key by combining part of one field with another. For example, in the
last name field is the name SAUNDERS and in the last four field are
the numbers 1234. I would like to take the first two letters of my
last name and combine them with the 1234 to create a unique field. So
this unique field (my primary key) will be SA1234. Is this possible?

 

[Jeff Boyce]

Anthony

!STOP!

A basic principle of database design is "one fact, one field". You do not
want to try to stuff two facts into a single field.

If what makes your records unique fits in two fields, use two fields as your
(multi-column) primary key. You are not limited to using just one field.

By the way, the names of people are notorious for not being unique. And
even if you are using something in your [last 4] field that you believe to
be unique (e.g., social security number), you risk trying to add duplicate
rows.

If you need a unique (and non-meaningful) row identifier, take a look at the
Autonumber data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bernard Peek]

In message

I am creating a database and I would like to create a unique primary
key by combining part of one field with another. For example, in the
last name field is the name SAUNDERS and in the last four field are
the numbers 1234. I would like to take the first two letters of my
last name and combine them with the 1234 to create a unique field. So
this unique field (my primary key) will be SA1234. Is this possible?

It's certainly possible but whether it's desirable is another question
altogether. You could extract the first two characters of one field and
store them in a new derived field then either append the contents of the
second field or just create a composite key.

This would de-normalise the table structure but although generally
undesirable that's not a hanging offence. The real problem is that there
are many different names that have the same first two characters, so
this part of the key doesn't contribute towards the uniqueness of the
key value. And if the numeric field alone is sufficient to guarantee
uniqueness then you don't require those two characters as a prefix.

There may be some reason why you particularly want to have a PK with
that format, but it isn't obvious from your post. Whatever the aim,
there are probably better ways to achieve it.

 

[Anthony]

Anthony

!STOP!

A basic principle of database design is "one fact, one field".  You do not
want to try to stuff two facts into a single field.

If what makes your records unique fits in two fields, use two fields as your
(multi-column) primary key.  You are not limited to using just one field.

By the way, the names of people are notorious for not being unique.  And
even if you are using something in your [last 4] field that you believe to
be unique (e.g., social security number), you risk trying to add duplicate
rows.

If you need a unique (and non-meaningful) row identifier, take a look at the
Autonumber data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am creating a database and I would like to create a unique primary
key by combining part of one field with another.  For example, in the
last name field is the name SAUNDERS and in the last four field are
the numbers 1234.  I would like to take the first two letters of my
last name and combine them with the 1234 to create a unique field.  So
this unique field (my primary key) will be SA1234.  Is this possible?- Hide quoted text -

- Show quoted text -

Thanks Jeff. So you do not suggest combining two fields. The field
that I was trying to create is what we call a Soldier's battle roster
number and it combines the first one or two letters of a last name
(depending on your command) with the last four of the SSN. These
battle roster numbers are typically unique but I can see that there is
a chance there could be a duplicate every now and again. I will use
the autonumber as a primary key. Thanks again.