Primary Keys
LurfysMa
Access help says that any unique keys will work.
What are the tradeoffs?
I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?
One table has information about the 50 states in the US. The table
looks like this:
State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912
Since the state names are unique, is there any reason not to make that
field the primary key?
Another table has to do with grade school multiplication tables. This
is a simple table something like this:
Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144
Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?
In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.
Thanks
--
Running MS Office 2000 Pro on Win2000
Barry Gilbert
usually work. The one place you would consider using an autonumber is if you
expected to have to change the other key value. I don't expect any states to
change their names any time soon, so it's probably ok there. In your other
example, is there any risk that you might change the values in the factors
field? On the other hand, even if you did need to change something, a
cascading update relationship would still accomodate this.
Bottom line: if you have a candidate field that, by its nature, is
guaranteed to be unique, use it.
Barry
Amy Blankenship
primary key or if you might ever want to edit that information. Since
primary keys are normally the way you establish relationships, you don't
want them to ever change once a record has been created. Even though you
may think the key value won't change, typos have been known to happen.
Also, number fields take up less space in the database and primary keys, as
the source of the relationship, are typically repeated over and over in many
tables.
Therefore, I always use autonumbers. Other opinions vary.
HTH;
Amy
"LurfysMa" <inv...@invalid.invalid> wrote in message
news:1f8ab25mq18uhqv6m...@4ax.com...
LurfysMa
<Amy_n...@magnoliamultimedia.com> wrote:
>In my opinion, you're asking for trouble if you ever show the user the
>primary key
Why is merely showing the user the primary key a problem?
RoyVidar
I'd recommend you to take the time to use your favourite search engine
for the terms like "natural vs surrogate primary key". Such search
will
probably list some of the pros and cons, in addition to hours of fun
;-)
Basically, some favours usage of surrogate keys (Autonumber), others
favours natural keys, which represents "things" having a business
meaning, and which can also be a combination of fields. Some (including
me) will use both, based upon the requirements. For state, I'd probably
use the two letter code.
Just be aware - for some this isn't just a matter of preference, it's
religion to a degree thats close to fanatism.
Just be sure that if you decide upon surrogate key (Autonumber), then
remember that this will not ensure the integrity of your data! It will
only ensure that each record has a unique number. Say in a table where
you have a unique field, but you decide to add an Autonumber field for
primary key, you will need to also add a unique index on the "natural
key" field in addition to the primary key index on the Autonumber
field,
else you'll risk dupes.
--
Roy-Vidar
KARL DEWEY
two letter abbreviation for the state would be a smaller index.
I have another case for your in that I have to keep training certifications
and occupational examination records on personnel. But they keep changing
departments, names, employee code when migrating to different subcontractors,
etc. I set up an alias table that will have all changes and you can see that
the database records reflect that Jane Doe, now married to Bill Smith, had
hearing exam last year. The data matches paper records. There is a new
alias record for every change and the front/top displays the latest always
with a subform showing the current and all previous aliases.
LurfysMa
<KARL...@discussions.microsoft.com> wrote:
>Primary keys build an index and if you are concerned with database size then
>two letter abbreviation for the state would be a smaller index.
Since there are only 50 states, the savings would be negligible even
if we annex Canada one day! ;-)
I was more interested in usage and reliability tradeoffs.
>I have another case for your in that I have to keep training certifications
>and occupational examination records on personnel. But they keep changing
>departments, names, employee code when migrating to different subcontractors,
>etc. I set up an alias table that will have all changes and you can see that
>the database records reflect that Jane Doe, now married to Bill Smith, had
>hearing exam last year. The data matches paper records. There is a new
>alias record for every change and the front/top displays the latest always
>with a subform showing the current and all previous aliases.
Sounds messy...
Amy Blankenship
with maintaining your data. When you show something to that type of user,
you lay it open to being changed. Here's a full discussion of the issue
http://www.dbpd.com/vault/9805xtra.htm
"LurfysMa" <inv...@invalid.invalid> wrote in message
news:4naab29a2jtsda1ec...@4ax.com...
Larry Linson
> Since there are only 50 states, the
> savings would be negligible even
> if we annex Canada one day! ;-)
Your mileage may vary, but I've never done a database using states where I
did not, sooner or later, need the state abbreviation, as well as other
information. And, just for the record, my much-used and
much-copied-from-database-to-database "state" lookup table is actually a
table of "US states and Canadian provinces" and, any day now, I may have a
client who will need Mexican states and their abbreviations, too.
In any case, since I need them anyway, I index on the state/province
abbreviation which may give a very minute performance advantage -- it
certainly will not be very significant.
Larry Linson
Microsoft Access MVP
Douglas J Steele
abbreviations have changed in Canada in recent memory (Quebec used to be PQ,
and now is QC, Newfoundland and Labrador used to be NF, and now is NL). We
also got a 3rd territory a few years back, but an addition to the table
isn't as bad as a change to the PK.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Larry Linson" <bou...@localhost.not> wrote in message
news:O2pg7zdp...@TK2MSFTNGP04.phx.gbl...
Jerry Whittle
the state of North Dakota tried to change its name to just Dakota a couple
years ago?
in the '70s there was a movement to split California into three states?
West Virginia was part of Virginia until the Civil War?
there's been attempts to make Puerto Rico a state?
Just goes to show that some things considered rock solid could change in the
future. That's why I like autonumbers for primary keys.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Albert D.Kallal
> Alabama Montgomery December 14, 1819
> Alaska Juneau January 3, 1959
> Arizona Phoenix February 14, 1912
>
> Since the state names are unique, is there any reason not to make that
> field the primary key?
Yes, there are several good reasons. First, you might find some spelling
errors. You might come out with a French version. Or, someone wants the
names to be spelled in German, or whatever tickles your fancy. If you use a
autonumber, and then start using a description for the State in place of the
name, then your database can continue to function without modification.
As others mentioned, there is much philosophy and strong views on each side
of the camp (natural keys vs autonumber keys).
My view is that when you relate a table, I simply want the database to
please give me a relation between those two tables I specify. At that
point, I give NOT one hoot about what field is used, and in fact I don't
even want to waste my brain power coming up with a field to create the
relaton. I want a one to many relaton. What you do after that is your
business!!
Here is my rant on this subject. It also explains why you don't every want
to expose the autonumber to the end user.
Be forewarned...this is a old post..and is a rant..but, it gives you the
idea of how much fervor can go into the subject...
----------------
Why would you EVE"R care what id ms-access uses for the relation?
Do you care what memory segment word gets loaded into? Do you
care if it is memory segment 32, or 8192?
Are you now to ask users with a prompt as to what memory locaton that your
word document will load into? Who cares..that junk is for comptuers to deal
with...not humans...
Who cares about a number you, and your users will NEVER see?
An autonumber is some mechanistic to generate a number. To you and me, all
we care about is that we have a relation from customers to customers invoice
table. Do we really care, or have to know what number is used?
Really, when word loads into memory, we don't care about the number used for
the memory location. Really, when ms-access has a relation between customers
and the invoice file...again we don't give a hoot about what number is used.
Me, or you never sees the segment number when word loads, and we as users
will never see the autonumber either.
These numbers are NOT for humans to see.....
There is a ZILLION kinds of internal numbers that your computer uses all day
to function. Why do you care what memory segment numbers the computer used
to load ms-access, or ms-word? Why care?
If your folks can see, or use those autonumbers...then that is your problem
with autonumbers.
The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.
With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.
If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).
Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number? Even if you change
the order number, again..why should your database not work? Maybe you need
to delete the order number? (again, what on planet earth does deleing some
arbitrary number like the order number HAVE ANYTHING to do with building a
functional relation between two tables? How possibility does these two
separate concepts have anything in common?).
You users should NEVER EVER see a autonumber.
You mistake here is to try and let humans see, or even refer to, or use the
autonumber in any way. Autonumbers are NOT to be given meaning by
humans...but ONLY to your software.
Who cars if you have a order number, or not? What does the fact of having a
order number have to do with your database to functionally correctly? If you
want to require that a order number HAS to be entered, then make the order
number a required field, but that simple stupid order number HAS NOTING to
do with setting up a relation between two tables.
Setup your relation between tables with internal numbers, and your database
will JUST WORK REGARDLESS of what fields, and things you decide to store as
data. Do not go and attached some number out of the blue like a stupid order
number to build relations between your tables. Can you imagine if products
like QuickBooks, or even products like ms-word exposed internal numbers used
for relations and other internal numbers as to how the software will
function? Software uses ZILLIONS AND ZILLIONS of internal numbers and
pointers to function.
Now that YOU ARE the software developer, it is up to you to hide these
numbers. You can expose these internal numbers (like autonumbers), but that
is just rude, and just services to torture your users. Hide all the internal
number stuff....every other developer before you did this....
Why expose users to the exhaust pipe of a car when all they want to do is
drive? Software is a machine you build. Build it...make it work, and then
give it to your users. Users do NOT need to know about the kinds of teeth
used in the gears for the car...
So, the two concepts of how relations works is that many of us just believe
that setting up a relation between two tables is a conceptual idea, and HAS
NOTING to do with the data that you need to store. Others would disagree on
this concept...
By the way, there are some STRONG augments for using natural keys. For
example, if I adopt a natural key in my data, then can freely move it
between TWO DIFFERENT systems that respect this approach. (of course, you
have to have those two systems respect that approach!!).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
http://www.attcanada.net/~kallal.msn
Lyle Fairfield
You could use whatever you want. IMO most of the criticism around this
topic involves autonumbers being used as the primary key, and in
addition, an attempt is made to use these autonumbers as ordinals,
perhaps sequential invoice numbers, rather than merely nominals,
identifiers of the records.
Many developers routinely create an autonumber ID in every table as
they create it. This, IMO, simplifies relationships (they are always
(ID, ID) where ID = ID), and ensures that a unique identifier exists
for each record, without concern for any meaning, duplication or
possible nullability of that identifier (Access forms often are not
updateable unless such an identifier exists).
But if one has the concepts and skill, other primary keys are fine. Of
course, many may not have the skill, and those who do will often choose
autonumbers to standardize their approach to this matter.
I use autonumbers. There are sufficient things to be planned and
decided about db design without including ... what will my primary keys
look like.
BTW, some think of Primary Key as something "special". A primary key is
simply the first created non-nullable unique index. Designating an
index as primary will move it to position one (or return an error). We
could easily do away with this term; I worked with indexes ( a thousand
times more powerful and useful than JET or SQL-Server indexes) for many
many years in the X-Base world without ever hearing it and I find no
particular value in its availability.
LurfysMa
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>Just to play devil's advocate, at least two of the official provincial
>abbreviations have changed in Canada in recent memory (Quebec used to be PQ,
>and now is QC, Newfoundland and Labrador used to be NF, and now is NL). We
>also got a 3rd territory a few years back, but an addition to the table
>isn't as bad as a change to the PK.
OK, OK. I'm sold. I'll use an autonum field as the primary key. I
suppose the phonetic English movement could still gather steam and
California would become Kaliforia or something. ;-)
rkc
> Most of the reference books recommend autonum primary keys, but the
> Access help says that any unique keys will work.
>
> What are the tradeoffs?
The tradeoff is that some people will think you're an
idiot if you use them and some people will think you're
an idiot if you don't.
As long as you understand that adding an autonumber
as a primary key has nothing to do with the normalization
process I think they are just fine.
Tony Toews
>Just be aware - for some this isn't just a matter of preference, it's
>religion to a degree thats close to fanatism.
<chuckle>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
hbinc
Nice discussions.
Every record that has an relation with other records in other tables,
must have an unique identification, that used in all the relations. As
long as the "meaning" of the record stays the same, this identification
stays the same. Whether it is Autonumbering or Random or whatever is
not important, as long as it is unique.
Independant of the the identification is what you choose as Primary
Key. This may be your unique identification, but in fact can be any
combination of any fields, as long as they do not contain null-values.
But if you use a non-Autonumbering Primary Key, be sure that you use
your unique identification in your relations!
HBInc.
onedaywhen
LurfysMa wrote:
> Most of the reference books recommend autonum primary keys, but the
> Access help says that any unique keys will work.
>
> What are the tradeoffs?
That is a good question.
He's the position, as I see it, in brief.
Codd introduced the idea of a primary key. He later realised that all
keys are valid and that he was previously thinking non-relationally
when he assumed one key would need to be nominated as 'primary'.
RM theory has since moved on from the concept of primary keys. It was
too late for SQL, though: SQL vendors implemented primary keys,
assuming the PK would be given special meaning, and the concept of PKs
was retro-fitted to the SQL standards.
You can replace all your PRIMARY KEY constraints with NOT NULL UNIQUE
because they logically equivalent. This is what the Access help means
as referred to by the OP. However, in terms of physical SQL
implementation, PRIMARY KEY has been given special meaning. This is why
you are (correctly) still urged to designate a PRIMARY KEY for all your
tables.
What few people tell you is *how* to choose the PK.
What it comes down to is this: for Access/Jet, what does PRIMARY KEY
give you that NOT NULL UNIQUE does not? What is the special meaning for
the particular product, Access/Jet?
The answer, for Access/Jet the PK determines the (non-maintained)
clustered index, the physical ordering on disk.
So the next question is: what makes the best clustered index? The
answer to this is that a clustered index favours BETWEEN clauses and
GROUP BY clauses in SQL DML (queries, etc). In other words, your choice
of PK in SQL DDL (design) is driven by you SQL DML (queries). The
paradox here is that you can't write SQL DML before you've written your
SQL DDL, so you need to keep your PK's under review.
If you've understood the above you should come to the conclusion that a
sole autonumber column will never make a good PRIMARY KEY in
Access/Jet, because a random/incrementing integer/GUID does not make a
good clustered index. I'd suggest that anyone who uses their autonumber
column in a BETWEEN or GROUP BY construct has got something wrong in
design and/or queries. I'd further suggest that anyone who uses BETWEEN
or GROUP BY constructs which do not include columns that comprise their
PKs are likely to have made a poor choice of PK.
Jamie.
--
Lyle Fairfield
> The answer, for Access/Jet the PK determines the (non-maintained)
> clustered index, the physical ordering on disk.
Can you verify this?
Lyle Fairfield
> What it comes down to is this: for Access/Jet, what does PRIMARY KEY
> give you that NOT NULL UNIQUE does not? What is the special meaning for
> the particular product, Access/Jet?
>
> The answer, for Access/Jet the PK determines the (non-maintained)
> clustered index, the physical ordering on disk.
From
http://msdn2.microsoft.com/en-us/library/wd9d69b1.aspx
THE CLUSTERED PROPERTY IS IGNORED FOR DATABASES THAT USE THE MICROSOFT
JET DATABASE ENGINE BECAUSE THE JET DATABASE ENGINE DOES NOT SUPPORT
CLUSTERED INDEXES.
onedaywhen
Lyle Fairfield wrote:
> > The answer, for Access/Jet the PK determines the (non-maintained)
> > clustered index, the physical ordering on disk.
>
> From
>
> http://msdn2.microsoft.com/en-us/library/wd9d69b1.aspx
>
> THE CLUSTERED PROPERTY IS IGNORED FOR DATABASES THAT USE THE MICROSOFT
> JET DATABASE ENGINE BECAUSE THE JET DATABASE ENGINE DOES NOT SUPPORT
> CLUSTERED INDEXES.
No need to shout.
Try reading more widely:
New features in Jet Version 3.0:
http://support.microsoft.com/default.aspx?id=137039
Quote: "Compacting the database now results in the indices being stored
in a clustered-index format. While the clustered index isn't maintained
until the next compact, performance is still improved ... The new
clustered-key compact method is based on the primary key of the table.
New data entered will be in time order."
ACC2000: Defragment and Compact Database to Improve Performance
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769
Quote: "A disk defragmenter will place all files, including the
database file into contiguous clusters on a hard disk ... If a primary
key exists in the table, compacting re-stores table records into their
Primary Key order. This provides the equivalent of Non-maintained
Clustered Indexes"
I think the phrase 'not supported' is used to convey the fact that in
Jet you cannot specify the clustered index independent of the PRIMARY
KEY as you can in, say, SQL Server. It may just mean that there is no
syntax for CLUSTERED INDEX.
Regardless of what 'not suuported' means, clustered indexes definitely
exist for Jet and PRIMARY KEY is the way to leverage them.
Jamie.
--
Jamie Collins
onedaywhen wrote:
> No need to shout.
BTW I tend to put SQL keywrods in uppercase e.g. PRIMARY KEY. Sorry if
you thought I was shouting.
Jamie.
--
David W. Fenton
news:1152873446.5...@h48g2000cwc.googlegroups.com:
> If you've understood the above you should come to the conclusion
> that a sole autonumber column will never make a good PRIMARY KEY
> in Access/Jet, because a random/incrementing integer/GUID does not
> make a good clustered index. I'd suggest that anyone who uses
> their autonumber column in a BETWEEN or GROUP BY construct has got
> something wrong in design and/or queries. I'd further suggest that
> anyone who uses BETWEEN or GROUP BY constructs which do not
> include columns that comprise their PKs are likely to have made a
> poor choice of PK.
A random PK would result in the placement of records on as many data
pages as possible, thus improving concurrency.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jamie Collins
David W. Fenton wrote:
> > If you've understood the above you should come to the conclusion
> > that a sole autonumber column will never make a good PRIMARY KEY
> > in Access/Jet, because a random/incrementing integer/GUID does not
> > make a good clustered index.
>
> pages as possible, thus improving concurrency.
Good point, I was thinking too narrowly.
If my table had columns for surname, initials and telephone number and
my queries predominantly use BETWEEN on the surname column, having the
table physically ordered on telephone number may make my queries
perform worse than if the physical order was on surname (can you
imagine trying to use a paper copy telephone directory ordered on
telephone number <g> ?!)
As I said, the choice of PK should be determined by the SQL DML e.g.
you are interested in page locks for updates in a multi-user
environment, I'm interested in query performance, etc.
Jamie.
--
Jamie Collins
Jamie Collins wrote:
> If my table had columns for surname, initials and telephone number and
> my queries predominantly use BETWEEN on the surname column, having the
> table physically ordered on telephone number may make my queries
> perform worse than if the physical order was on surname (can you
> imagine trying to use a paper copy telephone directory ordered on
> telephone number <g> ?!)
>
> As I said, the choice of PK should be determined by the SQL DML e.g.
> you are interested in page locks for updates in a multi-user
> environment, I'm interested in query performance, etc.
Oops! I meant to add:
In other words I want to fetch rows on the same page and contiguous
pages; you want to maximise the chances of the rows each user will be
interested in are on different pages (am I correct?) I think in my
simple contacts example physically ordering on surname would provide
good concurrency as well. Whatever, it's clear we are both thinking
about the Jet implementation (i.e. contiguous storage on disk) when
considering PKs. Can everyone else say the same?
Jamie.
--
Lyle Fairfield
> exist for Jet and PRIMARY KEY is the way to leverage them.
One can get some of the advantages of clustered indexes by choosing a
meaningful primary key, by compacting ... and, perhaps, by defragging.
This is a far cry from the convenience and power or a clustered index.
Jamie Collins
Lyle Fairfield wrote:
> One can get some of the advantages of clustered indexes by choosing a
> meaningful primary key, by compacting ... and, perhaps, by defragging.
We seem to be in agreement here i.e. what to consider when choosing a
PK.
> This is a far cry from the convenience and power [of] a clustered index.
The gap doesn't appear that wide to me but I'm willing to learn
otherwise. Details?
Thanks,
Jamie.
--
Lyle Fairfield
It appears wide to me. A clustered index in SQL-Server is maintained.
No compacting or defragging is required.
In Jet, if the table/file is small, the performance advantages will,
probably, not be noticed.
If the table/file is large, there will be a penalty (time/resources) in
compacting.
Even if we compact, there is, TTBOMK, no guarantee that the compacting
will use contiguous sectors, although we might hope for that if the
disk is new. Pages containing consecutive (from the pimary key point
of view) might be distant from each other (from the disk's point of
view). Defragging is likely to cure this. But defragging is slow. And
defragging may result in the space after the MDB/E being used by
another file. So that, as soon as we update or insert another record it
may again be remoteness (from the disk's point of view) from records
with "adjacent" primary keys. And to correct this remoteness we may
have to compact and defrag.
In a maintained clustered index all of this (we hope) is planned and
managed by the database engine.
This seems to me to be wide gap.
I understand that with a static database, the gap would be considerably
less.
In general, I agree with you that if one were doing a lot of sql work
based on >= 'Mainwaring; and <= 'Milne' it might, depending on other
needs of the db, it might be efficient to use some primary key that
helped with identifying the records wanted or processed.
I think I have said sufficient about this ...if you reply you shall
have the field to yourself.
David W. Fenton
news:1152879461....@p79g2000cwp.googlegroups.com:
Given that with Jet you can only have the one clustered index, I
really think this is a pretty irrelevant consideration. I never use
the BETWEEN operator on anything but date fields, which in the vast
majority of tables could not possibly ever be a candidate for PK
(and very seldom even a candidate for inclusion in a compound
natural key, which wouldn't give you the clustered index benefit,
anyway, unless the date was the first field of the compound key).
So, I just don't see any practical benefit in your pointing out the
performance advantage of the clustered index.
Have you tested SEEKs on non-PK indexes?
David W. Fenton
>
> Jamie Collins wrote:
>> If my table had columns for surname, initials and telephone
>> number and my queries predominantly use BETWEEN on the surname
>> column, having the table physically ordered on telephone number
>> may make my queries perform worse than if the physical order was
>> on surname (can you imagine trying to use a paper copy telephone
>> directory ordered on telephone number <g> ?!)
>>
>> As I said, the choice of PK should be determined by the SQL DML
>> e.g. you are interested in page locks for updates in a multi-user
>> environment, I'm interested in query performance, etc.
>
> Oops! I meant to add:
>
> In other words I want to fetch rows on the same page and
> contiguous pages; you want to maximise the chances of the rows
> each user will be interested in are on different pages (am I
> correct?) . . .
No. I want to minimize the chance that two users will be editing
data on the same data page.
> . . . I think in my
> simple contacts example physically ordering on surname would
> provide good concurrency as well. Whatever, it's clear we are both
> thinking about the Jet implementation (i.e. contiguous storage on
> disk) when considering PKs. Can everyone else say the same?
Well, either way, it's irrelevant for newly added records before the
database is compacted, since those are all going to land in their
own data page that is not written back in PK order.
As long as you've got records added after the last compact, there's
always going to be some data pages (and index data pages) that are
not in the final order (whether your natural key order or my random
Autonumber order), and thus the performance gain can never be fully
realized in an actively used database.
Jamie Collins
David W. Fenton wrote:
> As long as you've got records added after the last compact, there's
> always going to be some data pages (and index data pages) that are
> not in the final order (whether your natural key order or my random
> Autonumber order), and thus the performance gain can never be fully
> realized in an actively used database.
The table may not be updated frequently e.g. how often does your
telephone company send you a new paper copy directory, how frequent is
your subscription for a refreshed data set? I don't mean to argue
pointlessly, merely point out that you have to base your PKs (on a
table by table basis) on SQL DML which may or may not include frequent
SQL UPDATE statements.
Jamie.
--
Jamie Collins
David W. Fenton wrote:
> with Jet you can only have the one clustered index
Erm, think it is fundamental that a table can have only one clustered
index, regardless of SQL implementation. Surely a table with two
phyical orders is in fact tow tables!
> I never use
> the BETWEEN operator on anything but date fields, which in the vast
> majority of tables could not possibly ever be a candidate for PK
> (and very seldom even a candidate for inclusion in a compound
> natural key, which wouldn't give you the clustered index benefit,
> anyway, unless the date was the first field of the compound key)
You seem to have talked yourself into seeing my point i.e. make the PK
compound with first your date column followed by a candidate key. This
will favour your BETWEEN constructs.
Jamie.
--
Amy Blankenship
using. If, on the other hand, you use your PK's to establish relationships,
then physical location on disk is unimportant. With cheap fast processors,
labor time of the developer saved by using PK's that make development
efficient is far more cost-effective than any small speed gain realized by
trying to physically order records next to each other. Access is not the
tool of choice for DBA's obsessed with application speed ;-).
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1152890762.7...@b28g2000cwb.googlegroups.com...
David W. Fenton
> David W. Fenton wrote:
>> with Jet you can only have the one clustered index
>
> Erm, think it is fundamental that a table can have only one
> clustered index, regardless of SQL implementation. Surely a table
> with two phyical orders is in fact tow tables!
Yes. What I should have said was that Jet allows only the PK to be
clustered, not the indexed field of your choice.
>> I never use
>> the BETWEEN operator on anything but date fields, which in the
>> vast majority of tables could not possibly ever be a candidate
>> for PK (and very seldom even a candidate for inclusion in a
>> compound natural key, which wouldn't give you the clustered index
>> benefit, anyway, unless the date was the first field of the
>> compound key)
>
> You seem to have talked yourself into seeing my point i.e. make
> the PK compound with first your date column followed by a
> candidate key. This will favour your BETWEEN constructs.
But it's a nonsensical way to pick PKs. Dates are very seldom going
to be part of a natural key, at least not for very many types of
entities.
I think your suggestion is bloody stupid, as it optimizes something
that very seldom needs further optimization in the first place.
Jamie Collins
Amy Blankenship wrote:
> If it's that important to you, Access is probably the wrong database to be
> using. If, on the other hand, you use your PK's to establish relationships,
> then physical location on disk is unimportant.
Did you read the OP's post? They asked (paraphrasing), if I can use NOT
NULL UNIQUE to define my relationships, what do I need PRIMARY KEY for?
I certainly get you point but I'm trying to address another issue i.e.
what's the difference between NOT NULL UNIQUE and PRIMARY KEY in
Access/Jet to which the answer is the clustered index.
We need to differentiate between data integrity and indexing. I agree
that data integrity is vital, whereas indexing is less significant but
still important.
You should think of PRIMARY KEY as your most powerful index for a
table. If you are ignoring this aspect of PK then you are at best
missing out on some potential gain e.g. better performance, improved
concurrency, etc.
It sounds like you have no need for indexes: that's fine, that's your
choice and if your databases are small you will probably not notice any
difference. However, If you do use regular indexes yet don't consider
the indexing aspect of PRIMARY KEY then maybe it's time to reassess you
indexing strategy.
Jamie.
--
Jamie Collins
David W. Fenton wrote:
> Dates are very seldom going
> to be part of a natural key, at least not for very many types of
> entities.
It seems I was wrong then and you haven't had the epiphany yet.
Stop thinking in terms of PRIMARY KEY as being your primary key,
candidate key, natural key, etc because you can use NOT NULL UNIQUE for
those purposes. For Jet you must think in terms of PRIMARY KEY meaning
clustered index and nothing else, then choose whatever columns makes
sense in that context.
> I think your suggestion is bloody stupid, as it optimizes something
> that very seldom needs further optimization in the first place.
The important word there is 'seldom'. If 'optimization' is the *only*
thing that differentiates PRIMARY KEY from NOT NULL UNIQUE then why use
PK for any other purpose?
Jamie.
--
Amy Blankenship
advantages/disadvantages of using natural keys vs. autonumber. The words
not null unique did NOT appear.
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.
If my Access databases were large enough that indexing were an issue, they
wouldn't be in Access.
OK, I'll leave the church of the not null unique vs. primary key to you.
How many times do you kneel facing Redmond each day,anyway ;-)?
-Amy
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1152971717.3...@75g2000cwc.googlegroups.com...
polite person
<snip>
>
>If my Access databases were large enough that indexing were an issue, they
>wouldn't be in Access.
>
<snip>
I don't want to intrude in other people's point scoring but newbies might read this.
Indexing is essential in most Access databases. I think you probably mean "the efficiency of the
indexing."
Also the effectiveness of Access as against other dbs depends on other things besides size, as a
matter of fact Access can be used for pretty big databases.
Lyle Fairfield
> If my Access databases were large enough that indexing were an issue, they
> wouldn't be in Access.
You have Access Databases without tables or records? Cool!
Amy Blankenship
"polite person" <sn...@snippers.com> wrote in message
news:k3gib25se0l2a8jn1...@4ax.com...
> On Sat, 15 Jul 2006 13:44:34 -0500, "Amy Blankenship"
> <Amy_n...@magnoliamultimedia.com> wrote:
>
> <snip>
>>
>>If my Access databases were large enough that indexing were an issue, they
>>wouldn't be in Access.
>>
> <snip>
> I don't want to intrude in other people's point scoring but newbies might
> read this.
> Indexing is essential in most Access databases. I think you probably mean
> "the efficiency of the
> indexing."
Agreed. However, for most newbies and even many more advanced users, the
indexing Access does on its own is sufficient.
> Also the effectiveness of Access as against other dbs depends on other
> things besides size, as a
> matter of fact Access can be used for pretty big databases.
Sure, but if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.
Tim Marshall
> If my Access databases were large enough that indexing were an issue, they
> wouldn't be in Access.
>
> OK, I'll leave the church of the not null unique vs. primary key to you.
> How many times do you kneel facing Redmond each day,anyway ;-)?
It is Edgar Codd's legacy to which most of us pay homage, the platform
on which database development takes place is utterly irrelevant.
I haven't followed this thread, but I have seen this post. No offence,
but you've shown a complete and, if you are anything close to a
"professional" developer, shocking lack of understanding of relational
database design and of "Access" by:
1) Indicating indexing is not an issue; and
2) by referring to "Access databases".
As I said, if you consider yourself a database developer, all I can say
is...
wow.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Tim Marshall
> as a
>>matter of fact Access can be used for pretty big databases.
>
> Sure, but if you're trying to squeeze this kind of minute performance
> advantage out, Access isn't the right tool.
What irresponsible nonsense. Simply setting up a field with an index is
"squeezing"?. And minute performance? Perhaps with a couple of tables
of 1 to 10 records each... I suggest you do some development in the
real world and see how much difference there is in indexed joins versus
unindexed join fields. It's ridiculously easy to do this in "Access"
compared to say, Oracle. But in either platform, indexing is a simple
and very basic design principle.
No amount of rationalization for piss poor design someone might have
done in the past will change that fact.
David W. Fenton
> David W. Fenton wrote:
>> Dates are very seldom going
>> to be part of a natural key, at least not for very many types of
>> entities.
>
> It seems I was wrong then and you haven't had the epiphany yet.
>
> Stop thinking in terms of PRIMARY KEY as being your primary key,
> candidate key, natural key, etc because you can use NOT NULL
> UNIQUE for those purposes. For Jet you must think in terms of
> PRIMARY KEY meaning clustered index and nothing else, then choose
> whatever columns makes sense in that context.
I think you're mis-using your RDBMS if you treat it that way.
>> I think your suggestion is bloody stupid, as it optimizes
>> something that very seldom needs further optimization in the
>> first place.
>
> The important word there is 'seldom'. 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.
David W. Fenton
> Amy Blankenship wrote:
>> If it's that important to you, Access is probably the wrong
>> database to be using. If, on the other hand, you use your PK's
>> to establish relationships, then physical location on disk is
>> unimportant.
>
> Did you read the OP's post? They asked (paraphrasing), if I can
> use NOT NULL UNIQUE to define my relationships, what do I need
> PRIMARY KEY for?
No, you're completely wrong -- that isn't by any stretch of the
imagination the question the OP asked.
The poster simply asked about the pros and cons of surrogate vs.
natural keys.
> . . . However, If you do use regular indexes yet don't consider
> the indexing aspect of PRIMARY KEY then maybe it's time to
> reassess you indexing strategy.
Primary keys are important beyond their indexing and it's bloody
stupid to designate an index as a PK just so you get a clustered
index if it's *not* best candidate for the PK.
David W. Fenton
news:OnsFgKGq...@TK2MSFTNGP03.phx.gbl:
> . . . if you're trying to squeeze this kind of minute performance
> advantage out, Access isn't the right tool.
Oh, give me a break. Jet can handle millions of records just fine,
but without proper indexing, it wouldn't be usable.
Proper indexing is essential in *all* database engines.
And Jet is *not* a toy database in terms of data handling. It only
falls down in comparison to other databases in terms of handling
large numbers of simultaneous users and in terms of the size of the
data store.
Amy Blankenship
thread, which was the use of primary keys that are not unique identifiers of
the record in order to cluster them on disk, NOT simply indexing.
"Tim Marshall" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message
news:e9bvg5$ls9$1...@coranto.ucs.mun.ca...
Amy Blankenship
squeezing of performance. I was referring to the bizarre suggestion of
Jamie Collins that people pick primary keys not for their uniqueness but to
physically cluster the records on disk. This is a practice you, yourself
have taken issue with. Let me completely eliminate any possibility of
misunderstanding here for those unable to take things in context:
If you are so concerned with performance that you are picking your primary
key in order to physically cluster the records, you need to be using a
different database because Access, while a fine database and able to hold up
to pretty stiff requirements, was not build for _that_ type of fine tuning.
Gees!
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9801DAB1559DBf9...@127.0.0.1...
Amy Blankenship
"Tim Marshall" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message
> Amy Blankenship wrote:
>
>> If my Access databases were large enough that indexing were an issue,
>> they wouldn't be in Access.
>>
>> OK, I'll leave the church of the not null unique vs. primary key to you.
>> How many times do you kneel facing Redmond each day,anyway ;-)?
>
> It is Edgar Codd's legacy to which most of us pay homage, the platform on
> which database development takes place is utterly irrelevant.
>
> I haven't followed this thread, but I have seen this post. No offence,
> but you've shown a complete and, if you are anything close to a
> "professional" developer, shocking lack of understanding of relational
> database design and of "Access" by:
>
> 1) Indicating indexing is not an issue; and
>
> 2) by referring to "Access databases".
So, you're saying that multiple files created in access are not Access
databases? What do you call them then...?
Just wondering...
David W. Fenton
If that's what you meant to say, then that's what you should have
said.
I don't think you've really got grounds to complain, given how far
this nuanced statement of your position is from the original one I
replied to above.
David W. Fenton
> Whoah, whoah. Hang on. I was referring to the topic of this part
> of the thread, which was the use of primary keys that are not
> unique identifiers of the record in order to cluster them on disk,
> NOT simply indexing.
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.
David W. Fenton
We're not talking about Access databases here, but Jet.
D'oh.
Amy Blankenship
for instance, because that might not be unique but you might then want to
use it as a condition in a BETWEEN clause.
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns980297498D9C0f9...@127.0.0.1...
Amy Blankenship
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
Now this is just plain silly. The access file is actually a complete
application that contains, among other things, tables that _can be_
accessed by the Jet engine, 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.
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. 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?
-Amy
Rick Brandt
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns980297632D887f9...@127.0.0.1...
> >
>
> Now this is just plain silly. The access file is actually a complete
> application that contains, among other things, tables that _can be_
> accessed by the Jet engine, 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.
> 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. 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?
>
> -Amy
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"?
In addition to being able to use a database stored in an MDB file without using
Access one can also build an interface with Access to a non-Jet database like
SQL Server or Oracle. Would you call those "Access Databases"?
Pedantically, when most people talk about an Access Database, the *database* is
a Jet database and the *application* part is Access. I usually use the term
"Access/Jet" if I mean a database application consisting completely of MDB files
and "Access Application" if I am talking about the front end to any other
database engine.
For me the distinction only matters when the discussion concerns "engine level"
stuff. Discussions about keys, constraints, relationships etc., only make sense
when you are specific about whether the engine is Jet or something else.
As for your question, you would be using Access to create Jet indexes so they
would still apply when you interface with the database from another program.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Larry Linson
> 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
polite person
If you create a word document using open office it is still a word document. Or is it?
My god these threads could go on forever!
Lyle Fairfield
> If you create a word document using open office it is still a word document. Or is it?
> My god these threads could go on forever!
In the olden days an mdb file was a JET file. If it were created in VB
3's (and others) utility it would not have a reference to Access ...
someone who didn't know about Access might not even guess there was a
close relationship. Since Access objects have been stored in blobs I
don't know whether or not this is still pertinent and I'm not sure
about such things existing any more but my guess is that they do ... or
can. It would be kinda delusionary to call such a file an Access DB?
Amy Blankenship
"Rick Brandt" <rickb...@hotmail.com> wrote in message
news:Csxug.131634$dW3....@newssvr21.news.prodigy.com...
> Amy Blankenship wrote:
>> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
>> news:Xns980297632D887f9...@127.0.0.1...
>> >
>> > We're not talking about Access databases here, but Jet.
>>
>> Now this is just plain silly. The access file is actually a complete
>> application that contains, among other things, tables that _can be_
>> accessed by the Jet engine, 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.
>> 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. 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?
>>
>> -Amy
>
> 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"?
Absolutely. That's how I use most Access databases I create.
> In addition to being able to use a database stored in an MDB file without
> using Access one can also build an interface with Access to a non-Jet
> database like SQL Server or Oracle. Would you call those "Access
> Databases"?
Depending on who I am talking to about it. For most people, probably. Most
people I might need to talk to about it wouldn't understand the distinction,
orcare.
>
> Pedantically, when most people talk about an Access Database, the
> *database* is a Jet database and the *application* part is Access. I
> usually use the term "Access/Jet" if I mean a database application
> consisting completely of MDB files and "Access Application" if I am
> talking about the front end to any other database engine.
>
> For me the distinction only matters when the discussion concerns "engine
> level" stuff. Discussions about keys, constraints, relationships etc.,
> only make sense when you are specific about whether the engine is Jet or
> something else.
Again, depends on who you're talking to. Most people understand "Access
database" but couldn't care less what the Jet engine does.
> As for your question, you would be using Access to create Jet indexes so
> they would still apply when you interface with the database from another
> program.
Cool.
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns980297498D9C0f9...@127.0.0.1...
>> "Amy Blankenship" <Amy_n...@magnoliamultimedia.com> wrote in
>> news:usVZlbOq...@TK2MSFTNGP04.phx.gbl:
>>
>>> Whoah, whoah. Hang on. I was referring to the topic of this
>>> part of the thread, which was the use of primary keys that are
>>> not unique identifiers of the record in order to cluster them on
>>> disk, NOT simply indexing.
>>
>> 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.
>
> See, that right there argues against his point. You couldn't use
> a date, for instance, because that might not be unique but you
> might then want to use it as a condition in a BETWEEN clause.
Er, ever heard of compound keys?
David W. Fenton
> 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.
> . . . 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.
> . . . 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?
> 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.
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.
> . . . 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.
David W. Fenton
news:Z3yug.5319$k31.3923@trnddc06:
And that's a bad thing.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
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.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Amy Blankenship
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> "Amy Blankenship" <Amy_n...@magnoliamultimedia.com> wrote in
> news:OPvnZRR...@TK2MSFTNGP05.phx.gbl:
>> 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...
:p
Jamie Collins
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.
--
Jamie Collins
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.
--
Jamie Collins
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.
--
Terry Kreft
He, he, he, "Microsoft" and "Nomenclature" whenever I think of those two
words together it makes me laugh.
--
Terry Kreft
"Larry Linson" <bou...@localhost.not> wrote in message
news:Z3yug.5319$k31.3923@trnddc06...
David W. Fenton
>
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9802E7DCD5EE8f9...@127.0.0.1...
>> "Amy Blankenship" <Amy_n...@magnoliamultimedia.com> wrote in
>> news:OPvnZRR...@TK2MSFTNGP05.phx.gbl:
><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
news:1153121393....@h48g2000cwc.googlegroups.com:
> Please do take the time take the time to enumerate the differences
> FOR JET
No.
David W. Fenton
> 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.
Jamie Collins
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.
--
Jamie Collins
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
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" <jamiec...@xsmail.com> wrote in message
news:1153141231.9...@p79g2000cwp.googlegroups.com...
Jamie Collins
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/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
<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.
--
Jamie Collins
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.
--
Terry Kreft
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" <jamiec...@xsmail.com> wrote in message
news:1153146556.3...@h48g2000cwc.googlegroups.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.
>
> --
>
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)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Larry Linson
> 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.
David W. Fenton
news:ui7QTFcq...@TK2MSFTNGP05.phx.gbl:
> 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.
The reason I won't is that it would be rehashing material that's
been posted every time the topic of natural vs. surrogate keys has
come up. I don't see any point in posting something that would just
duplicate information that's been posted here dozens of times.
David W. Fenton
> To get a clustered index in Jet, I must leverage the PRIMARY KEY
> syntax.
You mis-spelled "mis-use PK syntax."
David W. Fenton
> I hope you can now understand my point and that any reply is a lot
> less confused/confusing than your previous one.
I'm done here. You're an idiot.
Jamie Collins
David W. Fenton wrote:
> The reason I won't is that it would be rehashing material that's
> been posted every time the topic of natural vs. surrogate keys has
> come up. I don't see any point in posting something that would just
> duplicate information that's been posted here dozens of times.
My question does not relate to natural vs. surrogate keys.
I want to know the difference in Jet between a column constrained as
NOT NULL UNIQUE and PRIMARY KEY. If there is existing subject matter on
this issue, please provide me with a link.
Jamie.
--
Jamie Collins
Terry Kreft wrote:
> 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.
My intention is to discourage readers from inferring anything from
someone who makes an assertion without reasoning.
Terry, Take another look. Could it be the case that David W. Fenton is
a subtle kind of troll: adds only snippets of information, does not
elaborate, posts intentionally confusing replies, puts more time and
effort into being rude than advancing the debate, calls everyone an
idiot before disappearing? My advice: don't try and do logic with a
troll.
Jamie.
--
Terry Kreft
I think there are lots of people, in CDMA especially, who display troll-like
attributes on occasion although on the whole I would not describe them as
trolls.
There are times when resorting to troll-like behaviour seems to be the only
way to get through to some people.
--
Terry Kreft
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1153214326.9...@35g2000cwc.googlegroups.com...
Jamie Collins
David W. Fenton wrote:
> retract the allegation.
OK, I retract the allegation. David, you are not bluffing. You have
your list of differences between NOT NULL UNIQUE and PRIMARY KEY. I am
truly sorry my underhand tactics have not resulted in you posting that
list here. While I'm about it, you are not a you troll; your point
about concurrency was welcome.
I hope I've shown I'll do much to get the correct information,
including being pleasant.
Jamie.
--
Jamie Collins
Larry Linson wrote:
> 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.
Consider these tables:
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
PRIMARY KEY (col1, col2)
);
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
UNIQUE (col1, col2)
);
AFAIK in Jet terms the only difference differences between the tables
is that the one with the PRIMARY KEY constraint will cluster on (col1,
col2) whereas the one with the UNIQUE constraint will not (i.e. retain
date/time inserted order).
There is a further point:
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER,
PRIMARY KEY (col1, col2)
);
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER,
UNIQUE (col1, col2)
);
The difference here is that although the one with the PRIMARY KEY
constraint can be created, no row where col2 is NULL can be inserted.
I do not think there are any further differences. If anyone thinks
there are, could they please post them here.
TIA,
Jamie.
--
polite person
>
>I think there are lots of people, in CDMA especially, who display troll-like
>attributes on occasion although on the whole I would not describe them as
>trolls.
>
>There are times when resorting to troll-like behaviour seems to be the only
>way to get through to some people.
CDMA hardly holds the record for troll-like behaviour!
David W. Fenton
[]
<PLONK>
David W. Fenton
> There are times when resorting to troll-like behaviour seems to be
> the only way to get through to some people.
Or not.
Jamie Collins
I supposed I should state another obvious difference:
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
PRIMARY KEY (col1, col2),
PRIMARY KEY (col1, col2)
);
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
UNIQUE (col1, col2),
UNIQUE (col1, col2)
);
The one that attempts two PRIMARY KEY designations is illegal
(reflecting the fact a table can only have one clustered index) whereas
the one with two UNIQUE constraints is legal.
Have I omitted anything.
Jamie.
--
Tim Marshall
> CDMA hardly holds the record for troll-like behaviour!
It's probably one of the best groups out there in usenet for generally
good rapport. A few notable exceptions notwithstanding.
Lyle Fairfield
> Jamie Collins wrote:
> > Consider these tables:
> >
> > CREATE TABLE Test (
> > col1 INTEGER NOT NULL,
> > col2 INTEGER NOT NULL,
> > PRIMARY KEY (col1, col2)
> > );
> >
> > CREATE TABLE Test (
> > col1 INTEGER NOT NULL,
> > col2 INTEGER NOT NULL,
> > UNIQUE (col1, col2)
> > );
> >
> > AFAIK in Jet terms the only difference differences between the tables
> > is that the one with the PRIMARY KEY constraint will cluster on (col1,
> > col2) whereas the one with the UNIQUE constraint will not (i.e. retain
> > date/time inserted order).
This is not my experience. I have found that when a Primary Key is not
created, but a Unique Not Null is, JET will treat the first Unique Non
Null in exactly the same way as a Primary Key, that is, on compact, it
will physically sort the records according to the first Unique Not Null
Index. I checked this a couple of days ago with a hex editor.
Of course, later specifying an index as "Primary Key" will shunt the
previous primary key to a position of lesse importance.
IMO "Primary Key" is a redundant term; it is simply a short way of
referring to the first created Unique Not Null Index. When I create
Tables with DDL I seldom bother to create a "Primary Key", but I do
create a primary key, that is I create a Unique Non Null Index.
Bri
>
> ...
> start_date DATETIME DEFAULT NOW() NOT NULL,
> end_date DATETIME,
> ...
>
> My start_date column has no time elements i.e. all start dates commence
> at midnight, making it suitable for clustering.
I'm not getting into this argument, but thought I should point out that
these two parts of your post are inconsistant. The Now() function
returns the Date and Time of the instant it is executed so these dates
will NOT commence at midnight. To do that you should use the Date()
function rather than the Now() function.
--
Bri
Jamie Collins
Bri wrote:
> thought I should point out that
> these two parts of your post are inconsistant. The Now() function
> returns the Date and Time of the instant it is executed so these dates
> will NOT commence at midnight. To do that you should use the Date()
> function rather than the Now() function.
Yes, you are indeed correct.
I tried to point this out myself in the post you are replying to. I'll
selective quote myself in an attempt to make the point clearer:
I think I may have mislead you by this:
start_date DATETIME DEFAULT NOW() NOT NULL,
I intended for start_date values to have no time element i.e.
start_date DATETIME DEFAULT DATE() NOT NULL,
I was coding off the top of my head and messed up! Apologies.
For a more considered version (but similarly coded on the fly), see
this:
http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc
Jamie.
--
Jamie Collins
Lyle Fairfield wrote:
> I have found that when a Primary Key is not
> created, but a Unique Not Null is, JET will treat the first Unique Non
> Null in exactly the same way as a Primary Key, that is, on compact, it
> will physically sort the records according to the first Unique Not Null
> Index. I checked this a couple of days ago with a hex editor.
>
> Of course, later specifying an index as "Primary Key" will shunt the
> previous primary key to a position of lesse importance.
Very interesting. I did not know that.
> IMO "Primary Key" is a redundant term; it is simply a short way of
> referring to the first created Unique Not Null Index. When I create
> Tables with DDL I seldom bother to create a "Primary Key", but I do
> create a primary key, that is I create a Unique Non Null Index.
I prefer an explicit syntax to one dependent on which NOT NULL UNIQUE
was created first. Ideally PRIMARY KEY would be a redundant term (as it
has effectively become IMO in SQL Server, for example) and we would
have an explicit CLUSTERED syntax (as for SQL Server, for example) .
However, because for Jet all we have is PRIMARY KEY then I will use it
(or misuse it, if you prefer <g>) to leverage the clustering
functionality.
Jamie.
--
Lyle Fairfield
> I prefer an explicit syntax to one dependent on which NOT NULL UNIQUE
> was created first.
Upon further review I believe the NOT NULL UNIQUE index with name first
in alphabetical order will substitute as primary key when there is not
Primary Key named.
Jamie Collins
Lyle Fairfield wrote:
> Upon further review I believe the NOT NULL UNIQUE index with name first
> in alphabetical order will substitute as primary key when there is not
> Primary Key named.
Alpha order of name sounds a worse approach than date created order!
Out of interest, have you tested whether a compound index will
substitute as primary key? What about when one of the columns is
nullable?
TIA,
Jamie.
--
Lyle Fairfield
not yet ...