Primary Keys

"Larry Linson" wrote in
news:Z3yug.5319$k31.3923@trnddc06:

"Rick Brandt" wrote

I believe David's point is that one can use
other programming environments like
VB to create an MDB containing tables
along with an entire application to
interface with that MDB all on a PC that
does not even have Access installed.
Would you still call that MDB an
"Access Database"?

Many do, including Microsoft.

And that's a bad thing.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Amy Blankenship" wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Amy Blankenship" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" wrote in message
. 1...
"Amy Blankenship" wrote in
:
snip

Now this is just plain silly. . . .

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.

. . . The access file is actually a complete
application that contains, among other things, tables that _can
be_ accessed by the Jet engine, . . .

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.

. . . and are accessed from inside the Access
application (with forms, reports, and module). However, you can
also access the database tables with other engines when calling
the *Access Database* from outside the Access application. In all
probability, you could probably call an Access Database file from
another Access Database file using a different engine, though I
have not tried it. It's on my to-do list.

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.

Saying that "Access Database" is not a valid way to refer to a
file created in the Access application containing tables that
_can_ be accessed by Jet is in my opinion a bit of a stretch. . .
.

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.

. . . However, it does bring up an interesting
question, one I don't claim to know the answer to: if you set up
your indexes from within Access, but then you call the file with
another driver, how do the indexes behave?

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

That's so informative. Very specific...

David W. Fenton wrote:
If 'optimization' is the
*only* thing that differentiates PRIMARY KEY from NOT NULL UNIQUE
then why use PK for any other purpose?

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.

--

David W. Fenton wrote:
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.

--

Amy Blankenship wrote:
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.

--

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


--

Terry Kreft


"Larry Linson" wrote in message
news:Z3yug.5319$k31.3923@trnddc06...
"Rick Brandt" wrote

I believe David's point is that one can use
other programming environments like
VB to create an MDB containing tables
along with an entire application to
interface with that MDB all on a PC that
does not even have Access installed.
Would you still call that MDB an
"Access Database"?

Many do, including Microsoft.

Larry

"Amy Blankenship" wrote in
:


"David W. Fenton" wrote in message
. 1...
"Amy Blankenship" wrote in
:
snip

Now this is just plain silly. . . .

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.

They aren't?

. . . The access file is actually a complete
application that contains, among other things, tables that _can
be_ accessed by the Jet engine, . . .

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.

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.

. . . and are accessed from inside the Access
application (with forms, reports, and module). However, you can
also access the database tables with other engines when calling
the *Access Database* from outside the Access application. In
all probability, you could probably call an Access Database file
from another Access Database file using a different engine,
though I have not tried it. It's on my to-do list.

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.

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

Saying that "Access Database" is not a valid way to refer to a
file created in the Access application containing tables that
_can_ be accessed by Jet is in my opinion a bit of a stretch. .
. .

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. . . .

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.

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 . . .

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.

. . . However, it does bring up an interesting
question, one I don't claim to know the answer to: if you set up
your indexes from within Access, but then you call the file with
another driver, how do the indexes behave?

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

That's so informative. Very specific...

You're an idiot, obviously.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Jamie Collins" wrote in
ups.com:

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

No.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/