Primary Keys

"Jamie Collins" wrote in
oups.com:

[]

PLONK

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

"Terry Kreft" wrote in
:

There are times when resorting to troll-like behaviour seems to be
the only way to get through to some people.

Or not.

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

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

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.

--

Jamie Collins wrote:
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.

Jamie Collins wrote:

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

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/micro...c3f233ba3336cc

Jamie.

--

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.

--

Jamie Collins wrote:
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.

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.

--

Jamie Collins wrote:
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?

not yet ...