Primary key

D

de Vroede

Hi,

I'm kinda new to db building and I was wondering why a primary key
should be an ID (AutoNumber). I have a table with users where the field
Username should also be unique. Why shouldn't I set the Username field
as primary key?

regards,

Jeroen
 
R

Rick Brandt

de said:
Hi,

I'm kinda new to db building and I was wondering why a primary key
should be an ID (AutoNumber). I have a table with users where the
field Username should also be unique. Why shouldn't I set the
Username field as primary key?
Click to expand...

Somewhat of a "religious" topic. There are fanatics on both ends of this
spectrum. Some that would NEVER use anything but natural data fields for their
PKs and others who just automatically use an AutoNmber on every table they ever
build and wouldn't consider doing anything else.

Theoritically, any field (or combination of fields) that is unique can be your
primary key. It is a big advantage though if the PK is also very unlikely to
ever change. This is not actually such a big deal in Access/Jet databases since
Jet supports building relationships which will automatically cascade updates and
deletions to PK values. Still, there are advantages to PKs which don't change.
"Natural" keys consisting of real data columns often fall short in that regard.

Also, with natural keys you often need to use more than one column to create a
unique value for a PK. There's nothing wrong with that (in theory), but it
means that you have to build more foreign key columns in related tables to build
the relationships.

For these reasons many people prefer using a numeric "surrogate" key for the PK
and for relating to other tables because it is always a single numeric column
that need never change (since it has nothing to do with the data). Of course,
you should still put unique indexes on the natural fields that would otherwise
have been your PK to maintain data integrity.

If you have a good natural candidate field for your PK there is no reason at all
not to use it as long as you consider the above and don't foresee any problems.

Personally I will use natural keys if I don't have to use more than two fields
(neither of which is a DateTime or imprecise Numeric) and where I can use RI or
triggers to cascade updates. If that criteria is not met, then I use a numeric
Surrogate field. This is not always an AutoNumber or IDENTITY field though.
 
D

Duane Hookom

I'll second that.

--
Duane Hookom
MS Access MVP

Larry Daugherty said:
"You pays your money and you makes your choice". There are often
hotly contested threads on the subject of natural keys versus
surrogate keys. Many of the arguments raised when the battle is
joined are specious and lend to clarity to the issue.

The first few databases I did on a contract basis I tried doing it
with natural keys. That required a little research and often getting
affirmation from the client that my candidate keys combined to
uniqueness within the application. Inevitably, always late in the
project, it would be discovered that "well, there are these few
exceptions". That meant going back and doing things over. I looked
into autonumber surrogate keys and have never looked back. There is
no time wasted with candidate keys and the customer has absolutely no
control over an autonumber. Never show the autonumber to the
customer. Never use an autonumber for anything except generating
uniqueness. It is not guaranteed to be sequential.

HTH

Welcome to the world of Access.
Click to expand...
 
L

Larry Daugherty

"You pays your money and you makes your choice". There are often
hotly contested threads on the subject of natural keys versus
surrogate keys. Many of the arguments raised when the battle is
joined are specious and lend to clarity to the issue.

The first few databases I did on a contract basis I tried doing it
with natural keys. That required a little research and often getting
affirmation from the client that my candidate keys combined to
uniqueness within the application. Inevitably, always late in the
project, it would be discovered that "well, there are these few
exceptions". That meant going back and doing things over. I looked
into autonumber surrogate keys and have never looked back. There is
no time wasted with candidate keys and the customer has absolutely no
control over an autonumber. Never show the autonumber to the
customer. Never use an autonumber for anything except generating
uniqueness. It is not guaranteed to be sequential.

HTH

Welcome to the world of Access.
 
R

Rick Brandt

mae said:
What if a user gets married? Or changes their name for some other
reason...?
Click to expand...

The database "purists" would likely say that what is most correct is not
always what is the least trouble for the developer/admin. The advantages of
surrogate keys are of a practical nature for the guy maintaining the
database and/or applicaitions that use it. Others in the organization might
not consider those advantages to have higher priority than other
considerations.

As to your direct question, doesn't cascade update eliminate that concern to
a large extent?
 
R

Rick Brandt

mae said:
What if a user gets married? Or changes their name for some other
reason...?
Click to expand...

The database "purists" would likely say that what is most correct is not
always what is the least trouble for the developer/admin. The advantages of
surrogate keys are of a practical nature for the guy maintaining the
database and/or applicaitions that use it. Others in the organization might
not consider those advantages to have higher priority than other
considerations.

As to your direct question, doesn't cascade update eliminate that concern to
a large extent?
 
C

Craig Alexander Morrison

Primary Keys should not be volatile however they need not be immutable.

Having said that People's Names are not the best Primary Keys because other
people could have the same name and they could involve several fields,
FirstName, MiddleName(s) (oops repeating group?), LastName.

Of course the OP mentioned Username and perhaps this is some assigned unique
value (a real world surrogate key, as it were).

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider
 
C

Craig Alexander Morrison

If your selected field Username is

1. not volatile (not likely to change very often, if at all)
2. not complex (not made up of more than a few fields)
3. small (small text fields or simple integer/long integer values)
4. familiar (known and understood by the user.
....oh...
5. Unique

....then use it for your Primary Key!

Even if you do use an AutoNumber or some other surrogate key make sure you
establish a Unique No Nulls index on this field provided it satisifies point
5.

BTW I would think that your field Username is actually a surrogate key,
albeit a familiar one.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider
 
J

Jerry Whittle

I have a table with users where the field Username should also be unique.

"Should" and "Will" are not the same thing. In your shoes, I'd have an
autonumber as the primary key and Username a unique constraint/index to
prevent duplicates. If you ever run into a duplicate Username, you'll have
some options, like removing the unique index, that won't be as painful such
as with a PK that's linked to other tables.

Of course if you enable Referential Integrity, Access can do a cascade
update on PK data that changes in the parent table.
 
T

Tim Ferguson

Of course the OP mentioned Username and perhaps this is some assigned
unique value (a real world surrogate key, as it were).
Click to expand...

A UserName is an entirely appropriate choice of key for a User. In our
organisation(*), Users are quite often reallocated to different real
people, therefore things like FName and LName and DoB and even JobTitle are
just attributes of the login.

It might or might not be a good choce for a table of People. The most
immediate concern to me would be how to enter a new employee that has not
yet been given login rights.

Database design is all about semantics.... <g>

(*) I'd like to mention that I have no role in their security!

All the best


Tim F
 
D

David Cox

The username should be unique, but will it be invariant? Will you get
requests to "change my username"?
 
Top