Primary Keys

[David W. Fenton]

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]

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]

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.

 

[Jamie Collins]

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]

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!

 

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

 

[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]

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.

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.

--

 

[Lyle Fairfield]

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.

 

[Jamie Collins]

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]

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]

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]

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?

not yet ...