Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Not Primary Key

Discussion in 'Information Technology' started by Tom Ellison, Jul 28, 2006.

Tags:
  1. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

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

    Similar Threads Forum Date
    Controversial Surat school puts primary kids to agni pariksha Hard Talk Apr 22, 2010
    Singh Teaches Primary Pupils About Sikh Culture Sikh Sikhi Sikhism Jan 31, 2009
    Sikh News Cold wave shuts Punjab primary schools for a week (New Kerala) Breaking News Jan 31, 2008
    A Group Of Scientists Placed 5 Monkeys In A Cage And In The Middle, A Ladder With Bananas Inspirational Stories Apr 6, 2015
    Gurus When Guru Gobind Singh Ji played Hockey (ਪੰਜਾਬੀ) History of Sikhism Apr 13, 2014

  3. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:O4Sxp7GeGHA.2068@TK2MSFTNGP02.phx.gbl...
    > 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
    >
    >
     
  4. 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.

    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:O4Sxp7GeGHA.2068@TK2MSFTNGP02.phx.gbl...
    > 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
    >
    >
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:O4Sxp7GeGHA.2068@TK2MSFTNGP02.phx.gbl
    > 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.

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

    (please reply to the newsgroup)
     
  6. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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" <dg@NOdataSPAMgnostics.com> wrote in message
    news:uA5OJuIeGHA.3932@TK2MSFTNGP05.phx.gbl...
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:O4Sxp7GeGHA.2068@TK2MSFTNGP02.phx.gbl
    >> 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.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  7. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:%23Zjeh$IeGHA.2416@TK2MSFTNGP03.phx.gbl
    > 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.

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

    (please reply to the newsgroup)
     
  8. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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" <dg@NOdataSPAMgnostics.com> wrote in message
    news:%23wwD6GJeGHA.4932@TK2MSFTNGP03.phx.gbl...
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:%23Zjeh$IeGHA.2416@TK2MSFTNGP03.phx.gbl
    >> 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.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  9. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:OKeAcLJeGHA.4304@TK2MSFTNGP05.phx.gbl
    > 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. :-/

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

    (please reply to the newsgroup)
     
  10. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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" <dg@NOdataSPAMgnostics.com> wrote in message
    news:OzKfNnJeGHA.1320@TK2MSFTNGP04.phx.gbl...
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:OKeAcLJeGHA.4304@TK2MSFTNGP05.phx.gbl
    >> 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. :-/
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  11. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:uwu1N1JeGHA.1320@TK2MSFTNGP04.phx.gbl
    >
    > 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.

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

    (please reply to the newsgroup)
     
  12. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Dirk:

    And thank you as well!

    Tom


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:eaLdH6PeGHA.564@TK2MSFTNGP02.phx.gbl...
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:uwu1N1JeGHA.1320@TK2MSFTNGP04.phx.gbl
    >>
    >> 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.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  13. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    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" <tellison@jcdoyle.com> wrote in message
    news:O4Sxp7GeGHA.2068@TK2MSFTNGP02.phx.gbl...
    > 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
    >
    >
     
  14. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM> wrote in
    message news:%231WKwCSeGHA.3792@TK2MSFTNGP03.phx.gbl...
    > 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" <tellison@jcdoyle.com> wrote in message
    > news:O4Sxp7GeGHA.2068@TK2MSFTNGP02.phx.gbl...
    >> 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
    >>
    >>

    >
    >
     
  15. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    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.


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:%23d3tsjSeGHA.3996@TK2MSFTNGP04.phx.gbl...
    > 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" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM> wrote
    > in message news:%231WKwCSeGHA.3792@TK2MSFTNGP03.phx.gbl...
    >> 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" <tellison@jcdoyle.com> wrote in message
    >> news:O4Sxp7GeGHA.2068@TK2MSFTNGP02.phx.gbl...
    >>> 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
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page