Not Primary Key

[Tom Ellison]

Dear friends:

I've just had an experience in Access that disturbs me.

A table is open in Design View, with the Indexes dialog showing. I select
the primary key and change the Primary attribute to No.

I get:

You can't change the primary key.

This table is the primary table in ome or more relationships.

If you want to change or remove the primary key, first delete the
relationship in the relationship window.

This makes no sense. A relationship can be on any unique index (on the
"one" side). It doesn't have to be on the Primary Key. Making an index not
primary has nothing to do with this!

It would be proper to prevent me removing the index, or making it not "No
Duplicates". But surely it does not HAVE to be the primary key.

Is my thinking correct?

Why would MS do this?

Tom Ellison

 

[Tom Ellison]

Add to this the fact that there is no current relationship to the Primary
Key! There is a relationship, but it is to another unique index.

Not that this should make any difference.

Tom Ellison

 

[Gary Townsend \(Spatial Mapping Ltd.\)]

Thats interesting yes you are right it has to be a unique key but doesn't
have to be a primary. I can't see too many people trying this but yes
strictly speaking it should allow it to be related as a candidate key
instead of a primary key.

Gary Townsend
Spatial Mapping Ltd.

 

[Dirk Goldgar]

Dear friends:

I've just had an experience in Access that disturbs me.

A table is open in Design View, with the Indexes dialog showing. I
select the primary key and change the Primary attribute to No.

I get:

You can't change the primary key.

This table is the primary table in ome or more relationships.

If you want to change or remove the primary key, first delete the
relationship in the relationship window.

This makes no sense. A relationship can be on any unique index (on
the "one" side). It doesn't have to be on the Primary Key. Making
an index not primary has nothing to do with this!

It would be proper to prevent me removing the index, or making it not
"No Duplicates". But surely it does not HAVE to be the primary key.

Is my thinking correct?

Why would MS do this?

Just a guess, Tom: maybe the indexes used by the relationship are
defined with respect to the primary key. So, instead of containing
direct record locators, those indexes contain the primary key, or
pointers into the primary key index. That's pure speculation, of
course.

 

[Tom Ellison]

Dear Dirk:

Not in the case I have here. The PK as defined on a single column. Another
single column unique index, on a different column, defines the "one" side of
the relationship.

I had no problem dropping the relationship, changing the formerly PK to be a
unique index, then recreating the index. But it seems a very wrong-headed
limitation to have to drop the relationship so you can do something that
doesn't affect, and isn't affected by, this index.

I'm proposing that MS has booted this badly.

Tom Ellison

 

[Dirk Goldgar]

Dear Dirk:

Not in the case I have here. The PK as defined on a single column.
Another single column unique index, on a different column, defines
the "one" side of the relationship.

I had no problem dropping the relationship, changing the formerly PK
to be a unique index, then recreating the index. But it seems a very
wrong-headed limitation to have to drop the relationship so you can
do something that doesn't affect, and isn't affected by, this index.

I'm proposing that MS has booted this badly.

That may well be, Tom, but I'm not sure you understood what I meant with
my guess, because your reply doesn't seem to me to be addressing it. I
realize that the key column in this relationship is not the table's
primary key column, and that therefore a different index is involved. I
was just speculating that *maybe* that other index is represented
internally in such way that it relies on the primary-key index, so that
the PK is necessary in order to resolve index lookups.

As I said, that's pure speculation, and I have no idea if it's the case
or not. It's just one way to explain the requirement -- an alternative
to the "Microsoft are boneheads" theory that is the leading contender.

 

[Tom Ellison]

Dear Dirk:

Thanks. I don't know what you're talking about. What kind of circumstances
can result in one index being "represented internally in such way that it
relies on the primary-key index, so that the PK is necessary in order to
resolve the index lookups"?

I can remove the PK, and restore the relationship. It still works. Does
that prove it isn't what you thought? I do that without touching the other
unique index.

I'm concerned enough to make a test to make sure I cannot change the other,
unique index to be not unique. That's what should be prohibited.

By the way, I wasn't dropping the PK index, just changing it to be not
Primary. Why would this be a problem even if it were involved in a
relationship? As long as it remains unique, it works the same for a
relationahip, right?

Tom Ellison

 

[Dirk Goldgar]

Dear Dirk:

Thanks. I don't know what you're talking about.

Neither do I; I'm just speculating.

What kind of
circumstances can result in one index being "represented internally
in such way that it relies on the primary-key index, so that the PK
is necessary in order to resolve the index lookups"?

One *could* build a secondary index that stores, along with the indexed
key, the primary key(s) of the record(s) holding that key. So locating
a record using this index would involve first looking in the the
secondary index to find the associated primary key value, and then
looking up that PK value in the primary key index to get a pointer to
the record. I'm not saying this is a smart way to structure a secondary
index, but I can imagine it.

I can remove the PK, and restore the relationship. It still works.
Does that prove it isn't what you thought?
Probably.

By the way, I wasn't dropping the PK index, just changing it to be not
Primary. Why would this be a problem even if it were involved in a
relationship? As long as it remains unique, it works the same for a
relationahip, right?

Should be. I don't know what's going on beneath the covers. Most
likely you're right, and it's just stupidity on the Jet programmers'
part that is giving rise to an mistaken error message. I was just
trying to come up with some alternative to that conclusion, but that was
probably wasted effort. :-/

 

[Tom Ellison]

Dear Dirk:

I greatly appreciate your imparting your wisdom on this. I've really been
wanting some of my expert friends to take a look at this, so I don't make
some mistake about it.

This is one of many pieces to a puzzle on which I propose to publish. It
may take a month or two just to study all these pieces. It gets bigger all
the time. I thought it would be a magazine article, or a seires. It looks
all the time more like a book.

What I'm more concerned about is this: Did they prevent changes to the
indexing that WOULD be detrimental to the function of a relationship, like
changing an NON-PK index to be not unique when it is on the ONE side of a
relationship. That would be bad. I'd rather have it be over-protected, and
perhaps a bit clumsy to work with, than to have it allow function-destroying
problems.

Sorry, but I don't know how to extend your speculation into something I can
test and write about. But you're always welcome speculate. Thanks for
helping sharpen my views on this. It is now about 3 pages of notes in my
outline for one of the chapters.

Tom Ellison

 

[Dirk Goldgar]

I greatly appreciate your imparting your wisdom on this.

You're welcome, Tom. Too bad I didn't have any to offer.

What I'm more concerned about is this: Did they prevent changes to
the indexing that WOULD be detrimental to the function of a
relationship, like changing an NON-PK index to be not unique when it
is on the ONE side of a relationship. That would be bad. I'd rather
have it be over-protected, and perhaps a bit clumsy to work with,
than to have it allow function-destroying problems.

Of course.

Sorry, but I don't know how to extend your speculation into something
I can test and write about.

Another way of saying it was dumb. That's okay, I never said I thought
it was a good one, merely an alternative.

Good luck with the article or book.

 

[Tom Ellison]

Dear Dirk:

And thank you as well!

Tom

You're welcome, Tom. Too bad I didn't have any to offer.


Of course.


Another way of saying it was dumb. That's okay, I never said I thought
it was a good one, merely an alternative.

Good luck with the article or book.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

['69 Camaro]

Hi, Tom.

This makes no sense. A relationship can be on any unique index (on the
"one" side). It doesn't have to be on the Primary Key.

The relationship doesn't have to be on the primary key, but one can run into
problems with queries when the relationship is established on a unique index
that isn't the primary key. A primary key won't allow NULL's, but a unique
index can, so one must be careful not to allow NULL's in this foreign key or
in the unique index of the foreign table.

Making an index not primary has nothing to do with this!

It does with Jet. One doesn't see this phenomenon with other relational
database engines, because those database engines usually don't automatically
create an index on the foreign key when referential integrity is enforced,
like Jet does -- wherein lies part of the problem. But there are several
things that tie in together that prevent you from doing this seemingly
irrelevant task.

By default, the foreign key constraint is assigned to the primary key of the
foreign table, not a unique index. One can avoid this by manually assigning
the relationship in the Relationships Window and enforcing referential
integrity on a unique index, even if there is no primary key assigned to the
table on the one side (the foreign table). For Jet's internal programming
purposes, the index automatically created for this foreign key when
referential integrity is enforced is considered to use the primary key of
the foreign table in the relationship, which is why the error messages you
see always refer to it as the primary key, even if no such key has been
created for the foreign table.

When you try to alter any of the characteristics of a primary key, such as
allowing duplicates, Jet will attempt to delete the primary key index,
because it doesn't qualify as a primary key without all of the required
charactersistics in place. Or when you try to alter the "pseudo primary
key" (the one referred to by the automatically created foreign key index on
the relationship with enforced referential integrity), Jet won't let you.
Jet creates the "~~temp~index~name0" index to replace the pseudo primary key
(without its uniqueness and/or ignoring NULL's properties) before attempting
to delete the index you are trying to alter -- because that foreign key
constraint requires an index, unless the constraint was defined as "no
index."

Jet's internal programming won't allow either of these index deletions
whenever there's a relationship dependency, so it refuses to let you make
the alteration -- until you get rid of that automatically created index on
the foreign key by removing the enforcement of referential integrity
(dropping the foreign key constraint) or by removing the relationship
altogether.

Is my thinking correct?

Never ask my opinion unless you _really_ want to know the answer. ;-)

Why would MS do this?

To allow people who don't know anything about relational databases to build
simple, working database applications with little effort. In other words,
to sell more products to the masses.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Ellison]

Dear 69:

Please understand that, in my instance, there was a relationship in which
this table was on the "one" side. However, that relationship was on a
column on which there was a unique index, but not the primary key. The
primary key wasn't involved in any relationship whatsoever. It therefore
makes no sense to me that it would be a problem to make any changes to the
primary key, making it a unique index or deleting it, because it isn't
involved in any relationship.

The point is, if there had been no relationship to this table, Jet wouldn't
complain. If there is a relationship, but it isn't on the primary key, why
should it complain then? It still makes no sense. I should be able to do
anything I like with the primary key if it isn't being used.

Does that make sense now?

Tom Ellison

 

['69 Camaro]

Hi, Tom.

The primary key wasn't involved in any relationship whatsoever.

By default, Jet uses the primary key of the table on the one side of the
one-to-many relationship as the foreign key in the table on the many side
when you enforce referential integrity. It doesn't really matter that this
isn't what you wanted. You get the default.

You can manually change this default assignment to a unique index on the one
side by using the Relationships Window to assign the relationship and
marking it to enforce referential integrity. However, Jet doesn't remove
the default assignment of the primary key as part of the relationship
between the two tables, meaning that you won't be able to change any
properties of this primary key (which would force Jet to drop the PrimaryKey
index on this column) while the foreign key constraint is in effect.

The point is, if there had been no relationship to this table, Jet
wouldn't complain.

It's not the relationship that makes a difference. It's whether or not
referential integrity is enforced, because when it is, a foreign key
constraint is placed on the two tables and the automatic index on the
foreign key is created, thereby restricting the database engine on what it
can do with the records in those fields, the properties of those fields, and
the indexes those fields are a part of. By "those fields," I mean the
primary key in the foreign table (on the one side), the "pseudo primary key"
(if it's the unique index that's been assigned to the relationship), and the
foreign key (on the many side). "Those fields" may consist of all three of
these I've listed or just the primary key and foreign key, but never just
the pseudo primary key and foreign key.

If there is a relationship, but it isn't on the primary key, why should it
complain then?

Jet didn't forget about the default primary key as part of the foreign key
constraint when it was created. Like Ragu spaghetti sauce, "It's in there,"
so you really can't separate it out from the rest of the ingredients without
throwing the whole jar out -- dropping the foreign key constraint.

It still makes no sense. I should be able to do anything I like with the
primary key if it isn't being used.

That's how Jet works. As far as Jet is concerned, this primary key in the
foreign table _is_ being used, so this index is restricted as long as the
foreign key constraint exists. If you want more flexibility in a database
engine, there are plenty of them out there, including free ones.

My suggestion is to use the default primary key, not just a unique index, as
part of the relationship if you want to enforce referential integrity
between the two tables.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.