FOREIGN KEY CONSTRAINTS

[Torrio]

Hello.
I am trying to create a new table in MS Access. It has to be done with a
script.
This table (MON_REQ) must have a foreign key (bldg_code) that references to
another table's primary key (BLDG.code).
I also want data in foreign field to be updated when the primary key is
updated. According to MS Help I wrote the following script:
create table MON_REQ (aa_number autoincrement primary key, bldg_code
text(8), req_date date, constraint FK_MON_REQ_building_code foreign
key(bldg_code) references BLDG(code) on update cascade).
When I execute it, I get a syntax error in Constraint clause and it marks
the word update.
What I am doing wrong?
Why it wrong since I found this clause in MS Access help?

Thank you

Torrio

 

[Granny Spitz via AccessMonster.com]

What I am doing wrong?

Only a few things:

1) Never use cascading updates in Jet. It's unreliable, so it may
compromise the referential integrity of your data. A failed update may leave
your data inconsistent, but you'll never know at the time of the failure
because there's no error message. For more info:
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

2) If you're using cascade updates on a table's primary key, then you have a
very strong argument for using a surrogate key on this table, because a
primary key should *never* change.

3) To use this syntax in SQL view, you must first set the "SQL Server
Compatible Syntax (ANSI SQL-92)" option for the current database. Note that
this syntax setting will probably affect other queries in your database
adversely, particularly those that use wildcards.

Why it wrong since I found this clause in MS Access help?

With the correct database setting, it will work in SQL view. It will also
work in VBA code that uses the ADO library, although not the DAO library, if
the database setting is not using "SQL Server Compatible Syntax."

 

[RoyVidar]

Hello.
I am trying to create a new table in MS Access. It has to be done
with a script.
This table (MON_REQ) must have a foreign key (bldg_code) that
references to another table's primary key (BLDG.code).
I also want data in foreign field to be updated when the primary key
is updated. According to MS Help I wrote the following script:
create table MON_REQ (aa_number autoincrement primary key, bldg_code
text(8), req_date date, constraint FK_MON_REQ_building_code foreign
key(bldg_code) references BLDG(code) on update cascade).
When I execute it, I get a syntax error in Constraint clause and it
marks the word update.
What I am doing wrong?
Why it wrong since I found this clause in MS Access help?

Thank you

Torrio

I think perhaps the on update cascade might be syntax which isn't
supported by Access, but by Jet. If this is the case, it will need
to be executed on an ADO connection (OLEDB provider). There's a bit
more information on Jet SQL in the following link, with additional
links to other articles (watch for linebreak in the
link)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Since you say you'll do this through scripting, I assume you're using
ADO anyway, so that shouldn't be a problem.

If you by scripting mean "have to do it with a stored query", then it
sounds like a school assignement.

The usual recommandation when working with Jet tables within Access,
is to use DAO.

 

[RoyVidar]

1) Never use cascading updates in Jet. It's unreliable, so it may
compromise the referential integrity of your data. A failed update
may leave your data inconsistent, but you'll never know at the time
of the failure because there's no error message. For more info:
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

I looked at your link, but I'm getting the impression that you're
interpreting Mr. Toews dislike of cascade updates as information
that it shouldn't be used in Jet. I dislike cascade updates, too,
but it hasn't given any problems the few times I've used it.

Would you have any more information supporting cascade updates
being so dangerous in Jet?

a primary key should *never* change.

If this were a requirement of the relational model, then the
implication would be that the relational model disallows natural
keys.

I don't believe that, but could you attribute it to some reliable
source? For instance Date or Codd?

 

[Granny Spitz via AccessMonster.com]

I'm getting the impression that you're
interpreting Mr. Toews dislike of cascade updates as information
that it shouldn't be used in Jet.

Cascading updates should not be used in Jet. They're unreliable. If any
records are locked during the update and *all* required updates can't be made
before the commit because of locks, then the whole transaction has to be
rolled back. Occasionally, the transaction isn't rolled back when it should
be (generally when there's not enough memory, but there are other
circumstances, too), and you're stuck with inconsistent data. And orphans.
And you don't know it unless you check if the cascade update was successful
or not.

I dislike cascade updates, too,
but it hasn't given any problems the few times I've used it.

Eventually, this practice will catch up with you. Cascade updates don't fail
very often, particulary for small tables, but I require a 100% success rate
to maintain data consistency and data integrity, and Jet hasn't been able to
deliver this rate in my experience.

If this were a requirement of the relational model, then the
implication would be that the relational model disallows natural
keys.

That's not the implication. An immutable primary key provides stability of
data structure, stable relationships to maintain referential integrity,
stable software applications that are dependent on the database's structure,
etc. Just because some natural keys may change doesn't mean that no natural
key should ever be used as a primary key. Sometimes a natural key is
appropriate, and sometimes a surrogate key is appropriate. Use your best
judgement.

could you attribute it to some reliable
source? For instance Date or Codd?

An immutable primary key? Sure. Tom Kyte, the Oracle guru, and Mike Lonigro:


Tom Kyte, Chapt. 3, "Expert Oracle, Signature Edition: Programming Techniques
and Solutions for Oracle 7.3 through 8.1.7": "... primary keys should be
immutable (we should never update them) ..."
http://www.dbazine.com/oracle/or-articles/kyte1/

Tom's answers to several questions on cascade updates from the "Ask Tom"
column on the Oracle site:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5773459616034


and Mike Lonigro: "... the primary key must be absolutely stable. It must be
protected in all cases from change. This does not mean protected from likely
change; rather, it must be guaranteed not to be allowed to change."
http://www.dbpd.com/vault/9805xtra.htm

 

[RoyVidar]

Cascading updates should not be used in Jet. They're unreliable. If
any records are locked during the update and *all* required updates
can't be made before the commit because of locks, then the whole
transaction has to be rolled back. Occasionally, the transaction
isn't rolled back when it should be (generally when there's not
enough memory, but there are other circumstances, too), and you're
stuck with inconsistent data. And orphans. And you don't know it
unless you check if the cascade update was successful or not.

Isn't it a bit difference between claiming this is unreliable and
should not be used in Jet

and

arguementing that such unreliability may occur *if* there is a lock on
som of the data *if* it at the same time also is a lack of memory or
*if* some other circumstances occur at the same time, this *might*
cancel a rollback.

When these circumstances occur, does it only apply to on update
cascade, or could it also apply to other types of updates or
regular maintanance? What about on delete set null, which is used and
recommended by at least some regular participants.

Eventually, this practice will catch up with you. Cascade updates
don't fail very often, particulary for small tables, but I require a
100% success rate to maintain data consistency and data integrity,
and Jet hasn't been able to deliver this rate in my experience.

Well, as I said, it still hasn't catched up, and when searching these
groups, I haven't found any reference to it catching up with anyone
else either. Are you the only one having this problem?

That's not the implication. An immutable primary key provides
stability of data structure, stable relationships to maintain
referential integrity, stable software applications that are
dependent on the database's structure, etc. Just because some
natural keys may change doesn't mean that no natural key should ever
be used as a primary key. Sometimes a natural key is appropriate,
and sometimes a surrogate key is appropriate. Use your best
judgement.

I'm trying to use my best judgement, and I have serious problems
understanding what you mean when you first claim "a primary key should
*never* change", then you say "Just because some natural keys may
change doesn't mean that no natural key should ever be used as a
primary key.".

So, what do you mean? Can they change or not? If you still claim
they cannot, then how do you enforce that on a natural key?

I'm wondering who invented immutability as a requirement for primary
keys. Is it based on the relational model (which I doubt), or is it
invented by surrogate key fan(atic)s to scare people away from
natural keys? I mean, if you choose to use natural keys, then
you know that sooner or later, one or more of it's values are
going to change...

An immutable primary key? Sure. Tom Kyte, the Oracle guru, and Mike
Lonigro:


Tom Kyte, Chapt. 3, "Expert Oracle, Signature Edition: Programming
Techniques and Solutions for Oracle 7.3 through 8.1.7": "... primary
keys should be immutable (we should never update them) ..."
http://www.dbazine.com/oracle/or-articles/kyte1/

Tom's answers to several questions on cascade updates from the "Ask
Tom" column on the Oracle site:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5773459616034


and Mike Lonigro: "... the primary key must be absolutely stable. It
must be protected in all cases from change. This does not mean
protected from likely change; rather, it must be guaranteed not to be
allowed to change." http://www.dbpd.com/vault/9805xtra.htm

Since you link to this, I must assume you put trust in it, and that
you call it a reliable source.

I think that your quote

"... the primary key must be absolutely stable. It must be protected
in all cases from change. This does not mean protected from likely
change; rather, it must be guaranteed not to be allowed to change."

again rules out natural keys, as I don't think there's any way to
guarantee that a primary key cannot change, unless you use a
surrogate key. When skimming through the article, it seems to me that
inventing characteristics of primary keys so that it excludes natural
keys, is the whole point of the article.

Here's his definition of primary key characteristics:

"To summarize, the primary key must be:
* Unique
* Not null
* Stable
* Different than the logical unique key
* Internally assigned
* Logically meaningless."

Curiously enough, that more or less defines a property/datatype just
like autonumber, identity .... what a coincidence ;-)

As an aside, with regards to the Jet identity (counter), I'm not sure
I'd dare say this is completely immutable either, or at least I don't
think it qualifies for Lonigros characteristics, as you can reseed it
and reuse the numbers.

Here's some Oracle guys discussing your article (watch for linebreaks
in the link)
http://blogs.ittoolbox.com/database/design/archives/the-case-for-the-surrogate-key-7574

 

[Granny Spitz via AccessMonster.com]

Are you saying anything that can go corrupt in Jet should be avoided?
Are autonumbers to be similarly avoided? Transactions?

No. I'm saying that the causes of those corruptions should be avoided. All
the AutoNumbers I've seen corrupted were due to the correct Jet service packs
not being installed. So to avoid the AutoNumber corruption, you don't avoid
AutoNumbers altogether, you just make sure that the latest service pack is
installed and that all computers that access an Access database application
are at the same service pack level.

Likewise, memo fields are commonly corrupted when bound forms edit records
containing those memo fields. One solution is to use an unbound text box for
the memo field and execute an update query in code when the rest of the
record is updated via the bound form. Another solution is to use a 1:1
relationship on a table and its partner table that holds the memo field, and
do a join on these two tables in a query that is used for a bound form. That
way, if the memo field gets corrupted during the record edit, at least only
the memo field is corrupted, not the rest of the record.

Address the causes and you can cure the symptom.

I don't wish to come over all aaron but a SQL platform that can't be
trusted to rollback a transaction would be worthless, wouldn't you
agree?

A relational database engine that doesn't use transaction logs *can't* be
trusted to rollback a transaction, but that doesn't make it worthless. Most
people who use Access have no idea what a transaction rollback or commit is,
but they find that their Access database application is very useful to them.
It suits their needs. On the other hand, if they were doing financial
transactions or mission-critical data operations, Jet would would be the
wrong database engine to use. They need the stability and robustness of a
client/server database engine that uses transaction logs in those situations,
where rollbacks are conducted properly to keep the data consistent.

 

[Granny Spitz via AccessMonster.com]

Isn't it a bit difference between claiming this is unreliable and
should not be used in Jet

and

arguementing that such unreliability may occur *if* there is a lock on
som of the data *if* it at the same time also is a lack of memory or
*if* some other circumstances occur at the same time, this *might*
cancel a rollback.

In your previous post, you asked: "Would you have any more information
supporting cascade updates being so dangerous in Jet?" I gave you the
results of our tests, the conclusion we drew, and the recommendation.
Cascade updates don't fail every time, but when they do fail, there's no
error message, leaving you with your pants down when your boss asks, "Why are
June's sales figures smaller this month than last month for the Chicago,
Minneapolis, and Boise offices when the other offices' sales haven't changed
and total sales haven't changed?" Saying, "It's not my fault. The cascading
update on the foreign key didn't cascade through all the records like it was
supposed to" won't clear up his confusion or give him confidence in the
veracity of your database application.

Well, as I said, it still hasn't catched up, and when searching these
groups, I haven't found any reference to it catching up with anyone
else either. Are you the only one having this problem?

No. It's been discussed twice before. To find those discussions, you'd need
to search the Google Group archive database that Google cleaned up and sped
up for the Google Groups Beta version that we're now using as the production
version. I'd estimate the current database available to the public is
missing about 10% to 15% of the Access Usenet messages from before the
conversion to the *new look.* But it's faster than the old one.

So, what do you mean?

I mean that I'm not going to beat a dead horse. The discussions on natural
keys vs. surrogate keys are available for your reading pleasure. If you want
to participate in those discussions, you don't need to invite me into the
fray too. I use natural keys when it makes sense, and I use surrogate keys
when it makes sense. If have a table of states in the US, I'll use the
natural key, the two-character abbreviation as the primary key, because that
makes sense to me. If I have a table of employees, then I'll use an EMPID
surrogate key and a unique index that doesn't allow NULLs on the natural key,
because that makes sense to me.

Why the dichotomy? Because those state abbreviations are very unlikely to
change during the lifetime of the database, but the last names of many of the
women in the employees table are going to change. When we change a female
employee's last name, we have to change it in one, and *only one,* place in
the entire database, which is the goal of a relational database and my goal
of *do it right the first time* so I don't have to take the time to fix
things repeatedly. The EMPID primary key in the employees table remains
unchanged and the EMPID foreign key in any other table remains unchanged when
we update a woman's last name, so we don't have to wait for a cascade update,
and I don't have to worry that all the changes that were supposed to happen
actually did. I don't get phone calls from users saying, "I can't find Nancy
Davis in the database. Didn't she get married? Do you know her new last
name?" because the applications those users are working with are keyed on the
surrogate key, not the employee's first and last names, MI and DOB.

I'm wondering who invented immutability as a requirement for primary
keys.

No one I know. I'm guessing it was some former telemarketer, whose last
unemployment check was already spent, was offered a job application at the
unemployment office as either a nuclear waste disposal technician, a belly
dancer or a database theorist, and the first two jobs had really fierce
competition.

Is it based on the relational model (which I doubt)

I haven't read all 333 rules yet, but my guess is that it's in there. Even
if it isn't, there's a logical reason for an immutable primary key. If you
use an immutable, meaningless surrogate key, then the natural key is stored
and updated in only one record in one table. Non-duplication of data is a
goal of relational databases in order to avoid data insertion and data
deletion anomolies, even if Dr. Codd didn't specifically mention immutable
primary keys in his paper in 1970. It was just a theory back then.

When skimming through the article, it seems to me that
inventing characteristics of primary keys so that it excludes natural
keys, is the whole point of the article.

He's trying to prove his point with real world examples. He can't very well
list the high tide times in Hawaii as proof of his point. He needed to
explain the problems he's encountered and had to deal with. I've encountered
many of the examples he describes in his article myself, so I wouldn't agree
that he *invented* disadvantages for natural keys.

Curiously enough, that more or less defines a property/datatype just
like autonumber, identity .... what a coincidence ;-)

You make it sound like these characteristics were invented *after* the
AutoNumber data type was invented, so that these characteristics would
coincide with the attributes of an AutoNumber.

As an aside, with regards to the Jet identity (counter), I'm not sure
I'd dare say this is completely immutable either, or at least I don't
think it qualifies for Lonigros characteristics, as you can reseed it
and reuse the numbers.

Just because you *can* reseed a counter doesn't mean that it's a requirement
for all counters, making this data type inherently unstable and therefore
disqualified for surrogate keys. Why would you reseed a meaningless number
so that you could reuse it in a column that requires unique values? You
wouldn't.

Here's some Oracle guys discussing your article

It's not my article. You asked me to find others who also recommended that
primary keys never change, because my opinion doesn't carry any weight with
you. And as for *Oracle guys,* I see *no* indication that any of the blog
commentators dabble in Oracle.

I'm not sure I want to discuss their discussion, if that was your intention
by offering that link. Their discussion of Mike Lonigro's article mostly
entails Eric's assertions, which I'm reluctant to put a label on. For
instance, Eric starts off by saying the article is full of holes, followed by
his comment about Mike Lonigro's statement, "The primary key, however, is a
physical design element that uniquely identifies rows after they are in the
database":

"No, candidate keys (of which primary keys are
a somewhat debatable subtype) are part of the
relational model, which is purely logical."

Which tells me Eric thinks primary keys aren't in the physical model.
Okaaaay. (None of the other commentators disputed Eric's theory or other
assertions, and just one of them, Gary, stated, "I agree with Eric above.")
Eric's comment on Mike's next statement, "[The primary key] is used in
creating foreign keys in other tables in order to establish the logical
relationships in the data model" is this:

"So a physical element is used to establish
'logical relationships'? Department of
Circularity Department?"

Which tells me Eric is trying to make fun of what he believes is Mike's fatal
error in developing the physical model before designing the logical model.
Okaaaay.

Discussing Eric's comments would seem to be a prelude to a debate on how many
angels can dance on the head of a pin. Please forgive me if I pass on this
intellectual opportunity.

 

[RoyVidar]

In your previous post, you asked: "Would you have any more
information supporting cascade updates being so dangerous in Jet?" I
gave you the results of our tests, the conclusion we drew, and the
recommendation. Cascade updates don't fail every time, but when they
do fail, there's no error message, leaving you with your pants down
when your boss asks, "Why are June's sales figures smaller this month
than last month for the Chicago, Minneapolis, and Boise offices when
the other offices' sales haven't changed and total sales haven't
changed?" Saying, "It's not my fault. The cascading update on the
foreign key didn't cascade through all the records like it was
supposed to" won't clear up his confusion or give him confidence in
the veracity of your database application.

Well, you said nothing about tests, you only said "Cascading updates
should not be used in Jet. They're unreliable".

Would you mind sharing those tests, so that others may reproduce your
results?

 

[RoyVidar]

I mean that I'm not going to beat a dead horse. The discussions on
natural keys vs. surrogate keys are available for your reading
pleasure. If you want to participate in those discussions, you don't
need to invite me into the fray too. I use natural keys when it
makes sense, and I use surrogate keys when it makes sense. If have a
table of states in the US, I'll use the natural key, the
two-character abbreviation as the primary key, because that makes
sense to me. If I have a table of employees, then I'll use an EMPID
surrogate key and a unique index that doesn't allow NULLs on the
natural key, because that makes sense to me.

Why the dichotomy? Because those state abbreviations are very
unlikely to change during the lifetime of the database, but the last
names of many of the women in the employees table are going to
change. When we change a female employee's last name, we have to
change it in one, and *only one,* place in the entire database, which
is the goal of a relational database and my goal of *do it right the
first time* so I don't have to take the time to fix things
repeatedly. The EMPID primary key in the employees table remains
unchanged and the EMPID foreign key in any other table remains
unchanged when we update a woman's last name, so we don't have to
wait for a cascade update, and I don't have to worry that all the
changes that were supposed to happen actually did. I don't get phone
calls from users saying, "I can't find Nancy Davis in the database.
Didn't she get married? Do you know her new last name?" because the
applications those users are working with are keyed on the surrogate
key, not the employee's first and last names, MI and DOB.

I asked about your claim that "a primary key should *never* change"
vs what you said in a later reply, and I used this exact wording

So, what do you mean? Can they change or not? If you still claim
they cannot, then how do you enforce that on a natural key?

To which you start your answer by saying that you do not want to
discuss natural vs surrogate keys, then you do exactly that (discuss
natural vs surrogate keys). But what on earth does your discussion
of natural vs surrogate keys have to do with a basic concept of
whether or not a primary key should be immutable? The only reference
I made to natural keys, was how to enforce such immutability should
one chose natural keys...

With regards to your sample, do you think it is likely that anyone
who can spell Access 101, at least some of the time, will actually
use a persons name as primary key? Or use first name, last name,
middle initial and date of birth as a composite primary key?

Anyway, you are aware that your sample is violating at least two of
the characteristics in your link, namely "logically meaningless" and
"different than the logical unique key". Why? Because

1 - when you display the surrogate to the user, you give it a
business meaning -> not longer "logically meaningless"

2 - when you allow the user to search by it, then the surrogate
becomes the logical key, hence the surrogate is no longer "different
than the logical unique key"

What's bitten me a couple of times when I've exposed surrogates like
this, is that what you show to the user, the user will want to
change ... ;-)

You will find most regulars in these NGs recommends to hide the
surrogates from the user, and only use it for relationship.

If you do not want to answer my questions, thats OK. But please, it
seems you invent questions that I haven't asked, invent objections
I haven't made, invent opinions I haven't voiced, which you answer.

 

[RoyVidar]

Just because you *can* reseed a counter doesn't mean that it's a
requirement for all counters, making this data type inherently
unstable and therefore disqualified for surrogate keys. Why would
you reseed a meaningless number so that you could reuse it in a
column that requires unique values? You wouldn't.

In the quote you adress, I have specifically addressed the Jet
identity (counter), or Access/Jet autonumber, if you like, further,
I state that I don't think it qualifies against Lonigros
characteristics. That does not mean that I disqualify it as a usable
surrogate key, neither have I expressed such views about similar
properties/datatypes of other platforms, it may however, give some
indications about what I think about Lonigros primary key
characteristics, and his article.

That one can reseed the Access/Jet autonumber, is trivial, and not
very interresting. Hovewer, in this context, it does violate at least
one of the characteristics of Lonigro, namely "logically
meaningless", because reseeding means it matters what the next number
is. Just to avoid this "personal stuff", I am talking about inherent
characteristics of the Access/Jet Autonumber field, not what I would
or would not do.

I said one can reseed it *and* reuse the numbers.

To reuse Access/Jet autonumbers, just do a simple insert. This
violates both the "internally assigned" characteristic of Lonigro,
because it is the developer who assign it, and the "logically
meaningless" characteristic of Lonigro, because the developer decided
to fill the gap/reuse "missing" numbers.

To take it even a step further, as far as I know, all long integers,
are valid. As long as the field is defined as primary key, or with a
unique index, you will only be able to insert unused numbers, but
without this, you can also insert dupes.

Whether or not this disqualifies the Access/Jet autonumber as a
surrogate key, is up to each developer to decide for herself. I've
only shown that it fails to meet Lonigros characteristics.

Now, before you go on about "why would you reuse autonumbers, or
insert or misuse autonumbers like that...", probably, I would not,
but it is a characteristic of the Access/Jet autonumber datatype.