Primary Keys

[David W. Fenton]

Many do, including Microsoft.

And that's a bad thing.

 

[David W. Fenton]

Most
people I might need to talk to about it wouldn't understand the
distinction, orcare.

You're not talking to those people when you post in this newsgroup.

 

[David W. Fenton]

Most people understand "Access
database" but couldn't care less what the Jet engine does.

Even when talking about PKs and RI? They may be *ignorant* and not
know that there's a distinction between Access and the Jet database
engine, but they still should *care* about the distinction. They
just haven't realized it yet.

Much like you, apparently.

 

[Amy Blankenship]

No, it's not the least bit silly. Discussion in this newsgroup would
go much more smoothly if people maintained the distinction between
Jet and Access in their posts. Often, it's necessary to sort out
what someone is trying to do and whether they are asking about an
Access problem or a Jet problem.

But in this case someone was asking about autonumber vs natural key. Hardly
an issue where fine semantical distinctions are important.

But indexes RI are *not* an Access feauture, but a Jet features. On
that level we are talking purely about Jet and not about Access at
all.

I don't think you can really talk about mdb files and leave Access
completely out of it.

Who gives a rat's ass?

Well, since we're discussing fine esoteric points, I expect everyone cares.
Since they care about every hair splitting semantical detail, apparently.

Well, the reason you're "upset" is because you have completely
misunderstood the point.

If I'm "upset" at all, it is because someone said that using the term
"Access databases" was some sort of indicator that I don't know what I am
talking about. That was very unprofessional on his part and unneccessary to
the discussion. IME, though, people who find it necessary to make that kind
of allegation have few skills of their own and feel it props up their own
reputation to try to tear down others'. Luckily, many if not most people
are astute enough to recognize that going around trying to tear others down
is at the least an indicaor of low self esteem.

One might conjecture, though, that someone who feels the need to respond to
*one* post several times is, in face, "upset", for whatever reason.

The subject of this thread is *not* an Access issue -- it has
nothing to do with the properties of Jet databases created by Access
that are specific to Access. It is entirely about properties of the
Jet database engine.

But the fact is that the database was created with Access (at least
hypothetically--none of this discussion seems to relate to an actual file).
Therefore it is an Access issue, whether it deals with part of Access or all
of Access.

Well, d'oh. Through Jet. And only the data is available.

That's so informative. Very specific...

 

[Jamie Collins]

Why would you think I believe that that's the only difference? I
certainly don't think so, and have never suggested as much.

I'm not going to take the time to enumerate the many differences, as
you just seem fixated on one subject, a very impractical and
ridiculous idea, it seems to me.

Please do take the time take the time to enumerate the differences FOR
JET, otherwise my short list will stand:

1) PK determines the (non-maintained) clustered index.
2) NULLs are not permitted in a PK column, which is unnecessarily
restrictive for compound PKs IMO, making NOT NULL UNIQUE more useful
that PK in Declarative Referential Integrity (DRI) - if you don't
understand the point, I can post an example.
3) Erm...
4) That's it!

If we were to expand the list FOR ACCESS:

1) It makes the PK column(s) bold in the 'Relationships' diagram.
2) Erm...
3) I can't think of anything else but no doubt there are others;
whether the collective Access benefits outweigh the loss of Jet
benefits is another matter...

Jamie.

--

 

[Jamie Collins]

Um, how do you set a non-unique index as a PK? The index has to be
unique to qualify as a PK, however artificially you've created it.

We seem to have covered this down thread - in fact, I think you
answered you own question - but just to be clear: you put the
non-unique column(s) first and a unique index next. It is often the
case of merely choosing the order of your natural key columns carefully
e.g. if I wanted to cluster on dates for this simply table (largely
ignoring data integrity constraints for simplicity):

CREATE TABLE SalariesHistory (
employee_number CHAR(10) NOT NULL,
start_date DATETIME DEFAULT NOW() NOT NULL,
end_date DATETIME,
salary_amount DECIMAL(15,4) NOT NULL,
CONSTRAINT SalariesHistory__natural_key_and_clustered_index
PRIMARY KEY (start_date, employee_number),
CONSTRAINT SalariesHistory__candidate_key
UNIQUE (end_date, employee_number)
);

and if I wanted to cluster on employee_number then I'd reverse the
columns:

PRIMARY KEY (employee_number, start_date)

As you favour an autonumber (ID) 'uniqueifier' (we'll continue to
disagree as to whether this is a true surrogate) then append this to
the end e.g.

CREATE TABLE SalariesHistory (
ID INTEGER IDENTITY(1, 1) NOT NULL,
employee_number CHAR(10) NOT NULL,
start_date DATETIME DEFAULT NOW() NOT NULL,
end_date DATETIME,
salary_amount DECIMAL(15,4) NOT NULL,
CONSTRAINT SalariesHistory__uniqueifier
UNIQUE (ID),
CONSTRAINT SalariesHistory__natural_key
UNIQUE (start_date, employee_number),
CONSTRAINT SalariesHistory__candidate_key
UNIQUE (end_date, employee_number),
CONSTRAINT SalariesHistory__clustered_index
PRIMARY KEY (start_date, ID)
);

to satisfy the conditions of good clustering and uniqueness (in that
order).

Note that

CONSTRAINT SalariesHistory__candidate_key
UNIQUE (end_date, employee_number),

above is an example of a candidate key which cannot be a PK in Jet
because the end_date colum is nullable, however the unique constraint
is still required for data integrity purposes i.e. a NULL end_date
indicates the current salary period and each employee can only have one
current salary period.

Jamie.

--

 

[Jamie Collins]

I think YOU need to reread it. The poster asked the
advantages/disadvantages of using natural keys vs. autonumber.

On re-reading I admit that is one interpretation. However, that
discussion is done to death on a regular basis and it quite boring.
Fortuitous, then, but I didn't intentionally go OT.

In your own words, indexing is less significant than data integrity (and,
presumably, developer time). Therefore, the things that are more
significant should be considered first.

Sure, the problem is that for you first is also last <g>. If you are
going the extra mile you may as well think holistically, it may save
some work. For example, first design your table:

CREATE TABLE Directory (
telephone_number VARCHAR(20) NOT NULL,
last_name VARCHAR(35) NOT NULL,
initial VARCHAR(9) NOT NULL
)
;

Next, determine your candidate keys:

ALTER TABLE Directory ADD
CONSTRAINT Directory__natural_key
PRIMARY KEY (telephone_number)
;

You admit you usually stop at this point. However, if you did continue
to consider the clustered index and decided to cluster on last_name you
would have to first undo your previous step:

ALTER TABLE Directory DROP
CONSTRAINT Directory__natural_key
;

ALTER TABLE Directory ADD
CONSTRAINT Directory__natural_key
UNQIUE (telephone_number)
;
ALTER TABLE Directory ADD
CONSTRAINT Directory__clustered_index
PRIMARY KEY (last_name, telephone_number)
;

So you may as well determine keys and the clustered index all in one
go!

Jamie.

--

 

[Terry Kreft]

He, he, he, "Microsoft" and "Nomenclature" whenever I think of those two
words together it makes me laugh.

 

[David W. Fenton]

But in this case someone was asking about autonumber vs natural
key. Hardly an issue where fine semantical distinctions are
important.

They aren't?

I don't think you can really talk about mdb files and leave Access
completely out of it.

Yes, you can. You can use Jet alone to create an MDB file
programatically. Michael Kaplan used to prefer to do this for his
Jet data files because the result was a much slimmer file with
tables that lacked custom Access properties that he didn't need.

So, you're wrong again.

Well, since we're discussing fine esoteric points, I expect
everyone cares. Since they care about every hair splitting
semantical detail, apparently.

The issue is completely unrelated to the distinction between Access
and Jet.

If I'm "upset" at all, it is because someone said that using the
term "Access databases" was some sort of indicator that I don't
know what I am talking about. . . .

When you're talking about purely Jet issue, yes, it's an issue that
you're not thinking clearly or that you're ignorant of the
essential distinction between Access and Jet.

. . . That was very unprofessional on his part and unneccessary to
the discussion. . . .

You're the one who came in and made sweeping pronouncements about
indexing (a Jet issue), yet you've now demonstrated that you don't
understand the most fundamental distinctions about how Access works.

. . . IME, though, people who find it necessary to make that kind
of allegation have few skills of their own and feel it props up
their own reputation to try to tear down others'. Luckily, many
if not most people are astute enough to recognize that going
around trying to tear others down is at the least an indicaor of
low self esteem.

What justification is there for the attitude you threw with your
*wrong* declarations about Access performance and indexes?

One might conjecture, though, that someone who feels the need to
respond to *one* post several times is, in face, "upset", for
whatever reason.

No, not at all. One thing that happens when I read your posts is
that I come across one thing that I think can't be topped in its
stupidity, respond just to that part, and then reading on find out
that you've topped the original absurdity.

But the fact is that the database was created with Access . . .

Which is entirely irrelevant to the question at hand. . .

. . . (at least
hypothetically--none of this discussion seems to relate to an
actual file). Therefore it is an Access issue, whether it deals
with part of Access or all of Access.

No, you're just plain wrong.

That's so informative. Very specific...

You're an idiot, obviously.

 

[David W. Fenton]

Please do take the time take the time to enumerate the differences
FOR JET

No.

 

[David W. Fenton]

We seem to have covered this down thread - in fact, I think you
answered you own question - but just to be clear: you put the
non-unique column(s) first and a unique index next.

But that's *not* a non-unique index. That's inserting a non-unique
field into a compound key in order to artificially create a unique
key. Since the clustered index is written in the order of the
compound key, if you put the non-unique field first, the index and
data will be written in the order of the first field.

But it's still not a non-unique *index*. It's just a non-unique
field participating in a compound index.

 

[Jamie Collins]

No.

I therefore conclude there are none.

Jamie.

--

 

[Terry Kreft]

Which says a bit more about you really.

David is not here to spoon feed you or anyone else, if he chooses not to
answer that is his prerogative. For you to make a totally unrelated
conclusion from that shows either fallible logic on your part or a
determination to appear foolish.

 

[Jamie Collins]

But it's still not a non-unique *index*. It's just a non-unique
field participating in a compound index.

I can't make up my mind whether you are confused, deliberately trying
to confuse the point or just have a confusing way with the English
language <g>.

I think I may have mislead you by this:

....
start_date DATETIME DEFAULT NOW() NOT NULL,
end_date DATETIME,
....

I intended for start_date values to have no time element and for
end_date to be one granule of time before the next contiguous time
duration i.e.

....
start_date DATETIME DEFAULT DATE() NOT NULL,
CONSTRAINT salary_start_date__closed_period
CHECK (
HOUR(start_date) = 0
AND MINUTE(start_date) = 0
AND SECOND(start_date) = 0
),
end_date DATETIME,
CONSTRAINT salary_end_date__open_period
CHECK (
HOUR(end_date) = 23
AND MINUTE(end_date) = 59
AND SECOND(end_date) = 59
), ...

not to mention constraints to ensure non-overlapping periods for the
same employee, contiguous periods, ensuring the salary amount changes
between contiguous periods, etc but I wanted to keep it simple.

I'll try and be clear: my aim is to create a clustered index on
start_date (rather than not creating a not non-unique index on nothing
<vbg>).

To get a clustered index in Jet, I must leverage the PRIMARY KEY
syntax.

Defining a PRIMARY KEY *constraint* causes the engine to create a
(non-maintained) clustered *index*.

My start_date column has no time elements i.e. all start dates commence
at midnight, making it suitable for clustering.

My start_date column is not unique in the table because more than one
employee can start receiving a salary amount effective on the same day.


To use start_date in the PK I need to append (i.e. to the right in the
PK definition) additional column(s) that will 'uniqueify' the
start_date column; this could be an autonumber column (if used) or
employee_number.

Beyond start_date it really doesn't matter which columns are included
as long as the compound is unique; put another way, I don't care that
the engine needs something as a 'tie breaker' for when the start_dates
coincide, I just want them to be clustered together.

I hope you can now understand my point and that any reply is a lot less
confused/confusing than your previous one.

Jamie.

--

 

[Jamie Collins]

David is not here to spoon feed you or anyone else,

if he chooses not to
answer that is his prerogative.

Not replying is one thing but for him to post back to say he is not
going to answer me...?

For you to make a totally unrelated
conclusion from that shows either fallible logic on your part or a
determination to appear foolish.

Terry, I'm merely suspicious of someone who repeatedly takes the time
to say (quote), "I'm not going to take the time to enumerate the many
differences."

I genuinely believe the man is bluffing. You no doubt recall the last
time we had this attitude from him
(http://groups.google.com/group/microsoft.public.access/msg/499225a5a30a2caa)
i.e. he alludes that he 'knows something' but won't deign to post it.

I'm sure everyone knows I'm not afraid of being made to look foolish

Which says a bit more about you really.

I hope the message about me is, 'I will assert this position until
someone can show me I'm wrong.'

I don't think there is much credit in saying (paraphrasing), 'I know
you are wrong but I will not attempt to demonstrate it.'

Jamie.

--

 

[Jamie Collins]

David is not here to spoon feed you or anyone else

Actually, rather than the flippancy of my original reply, I think I
should address that point.

I don't expect to be spoon fed, rather I would like people provide
something substantial (code demonstration, link to a MSDN article, etc)
to back up their assertions. I usually do so up front and always do so
when challenged, and humble pie is often dish of the day.

David has not even put up his short list, let alone tried to make it
stand up.

I'm put in mind of Fermat: "I have a truly marvellous proof of this
proposition which this margin is too narrow to contain."

Jamie.

--

 

[Terry Kreft]

But that's David's choice whether he supplies that or not, you are free to
make any inference you like from his failure to reply but if you post that
inference then you leave yourself open to challenge. Your concluding remark
was not a reasonable conclusion from David's refusal to answer.

 

[Tim Marshall]

I genuinely believe the man is bluffing. You no doubt recall the last

Ummm, David Fenton bluffing? He very commendably doesn't stoop to flame
wars, so you must be talking about his Access/Jet/VBA knowledge. In
which case, all due respect to you, Jamie, I'm afraid I'd have to
conclude your belief is erroneous. 8)

 

[Larry Linson]

"Jamie Collins" wrote

I genuinely believe the man is bluffing. You
no doubt recall the last time we had this
attitude from him

As Tim has said, "Your belief is erroneous."

David does not "tolerate fools gladly" and his words are sometimes sharper
than I might write, but he "knows his stuff" when it comes to Access and
Jet.

Larry Linson

 

[David W. Fenton]

I genuinely believe the man is bluffing. You no doubt recall the
last time we had this attitude from him
(http://groups.google.com/group/microsoft.public.access/msg/499225a
5a30a2caa) i.e. he alludes that he 'knows something' but won't
deign to post it.

MessageIDs, please.

Otherwise, retract the allegation.