Primary Keys

"Jamie Collins" wrote in
oups.com:

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.

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.

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

David W. Fenton wrote:
Please do take the time take the time to enumerate the differences
FOR JET

No.

I therefore conclude there are none.

Jamie.

--

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.


--

Terry Kreft


"Jamie Collins" wrote in message
oups.com...

David W. Fenton wrote:
Please do take the time take the time to enumerate the differences
FOR JET

No.

I therefore conclude there are none.

Jamie.

--

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

--

Terry Kreft wrote:
David is not here to spoon feed you or anyone else,

Hey, plenty of people come here to get spoon fed. Why not me g?

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/micro...9225a5a30a2caa)
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
g.

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.

--

Terry Kreft wrote:
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.

--

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.


--

Terry Kreft


"Jamie Collins" wrote in message
oups.com...

Terry Kreft wrote:
SNIP
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.


Jamie.

--

Jamie Collins wrote:

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)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

"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

"Jamie Collins" wrote in
oups.com:

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/micro...ss/msg/499225a
5a30a2caa) i.e. he alludes that he 'knows something' but won't
deign to post it.

MessageIDs, please.

Otherwise, retract the allegation.

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