How does Access change primary key value within an RI relationship?

[Dean Slindee]

We are using .adp projects with a SQL Server backend. Lookup tables
typically have a single field for
the key to a lookup table that populates combo box lists.

These lookup values are stored "as is" in the detail data tables, and RI'd
back to the lookup tables. Example: lookup table primary key values for a
Color table, primary Key (ColorID): Red, Brown, Blue, Green, etc.

Data table has a column named ColorID that is RI'd to lookup table, with
Cascade Update ON.

What or how does Access do underneath the covers when I change the "Red"
value in the lookup table to "Rust"?
I want to be able to do the same process in VB.NET (change primary key
value), rather than do a Insert/Delete, rebuild RI and Cascade Update
settings.

Thanks,
Dean S

 

[Tony Toews]

We are using .adp projects with a SQL Server backend. Lookup tables
typically have a single field for
the key to a lookup table that populates combo box lists.

These lookup values are stored "as is" in the detail data tables, and RI'd
back to the lookup tables. Example: lookup table primary key values for a
Color table, primary Key (ColorID): Red, Brown, Blue, Green, etc.

Data table has a column named ColorID that is RI'd to lookup table, with
Cascade Update ON.

What or how does Access do underneath the covers when I change the "Red"
value in the lookup table to "Rust"?

Access changes the value in the Colour table and changes all the
values in the foreign keys as well. I'm sure this is all bound in a
transaction so that if the update fails for any reason that all the
changes can be roll backed.

Note though that I much prefer using autonumber primary keys for every
table so that you don't have to worry about Cascade Updates.

Several other former and current MVPs disagree with me and prefer the
lookup tables as you have described. <smile>

Also note that this discussion can sometimes be referred to as
religious or fanatical. Not quote but close. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Jamie Collins]

I much prefer using autonumber primary keys for every
table so that you don't have to worry about Cascade Updates.

Several other former and current MVPs disagree with me and prefer the
lookup tables as you have described. <smile>

Also note that this discussion can sometimes be referred to as
religious or fanatical. Not quote but close. <smile>

Here's a friendly tip from a moderate, secular, non-MVP: you should
make it clear that the natural key should additionally be constrained
as UNIQUE, otherwise someone like me may feel compelled to point out
that your autonumber 'primary key' will fail in its job to provide
effective protection from duplicates. Whether this would help avoid the
whole 'Jet's meaning of PRIMARY KEY to determine the non-maintained
clustered index for the table' thing, I cannot say <smile>

Jamie.

--

 

[Jamie Collins]

What or how does Access do underneath the covers when I change the "Red"
value in the lookup table to "Rust"?

Usually, 'Access' is synonymous with 'Jet' but this is one of the
occasions where a making a distinction is beneficial. Access's CASCADE
referential actions are implemented in Jet 4.0.

The Jet 4.0 implementation should follow the ANSI-92 specification:

X3H2-92-154/DBL CBR-002 11.8 <referential constraint definition>:
"If an <update rule> is specified and a non-null value of a referenced
column in the referenced table is updated to a value that is distinct
from the current value of that column,...if the <update rule> specifies
CASCADE, then in all matching rows the referencing column that
corresponds with the referenced column is updated to the new value of
the referenced column."

Note I am not aware of Microsoft or anyone else declaring whether or
not Jet 4.0 is compliant with ANSI-92 as regards CASCADE actions.

I want to be able to do the same process in VB.NET (change primary key
value), rather than do a Insert/Delete, rebuild RI and Cascade Update
settings.

Note a CASCADE action additionally be applied to columns constrained by
a UNIQUE constraint. A UNIQUE differs from that of 'primary key' in
that a UNIQUE constraint can comprise NULL values.

Also note that an UPDATE is implemented by a DELETE followed by an
INSERT.

Jamie.

--

 

[Brendan Reynolds]

Jamie, if you'll look again at the original post, this is an ADP. JET is not
involved.

In this scenario, it is SQL Server that is performing the cascade action,
and, to the best of my knowledge based on the information available, I would
expect SQL Server to go right on doing that regardless of whether the UI is
an Access ADP or a VB.NET app. I know of no reason why the original poster
should need to do anything special to achieve this in the .NET app.

 

[Jamie Collins]

Jamie, if you'll look again at the original post, this is an ADP. JET is not
involved.

You're right!

In this scenario, it is SQL Server that is performing the cascade action,
and, to the best of my knowledge based on the information available, I would
expect SQL Server to go right on doing that regardless of whether the UI is
an Access ADP or a VB.NET app.

Substitute 'SQL Server' for 'Jet 4.0' and 'full SQL-92' for 'ANSI-92'
and everything I said still applies <g>.

I thought the OP was implementing their own cascade update actions in
their .NET app.

Jamie.

--

 

[Brendan Reynolds]

I thought the OP was implementing their own cascade update actions in
their .NET app.

It does sound that way, yes. But if that is the case, then I think we need
to ask 'why?' Perhaps there is a valid reason. Or perhaps the original
poster was not aware that SQL Server could do it.

 

[Jamie Collins]

It does sound that way, yes. But if that is the case, then I think we need
to ask 'why?' Perhaps there is a valid reason. Or perhaps the original
poster was not aware that SQL Server could do it.

Well, Jet 4.0 and SQL Server (all versions) are different in this
respect e.g. consider the following:

CREATE TABLE Test (
col1 INTEGER NOT NULL UNIQUE,
col2 INTEGER
REFERENCES Test (Col1)
ON UPDATE CASCADE
);

Works fine on Jet 4.0 whereas SQL server needlessly chokes. Perhaps the
OP is asking, 'Why the difference...?'

Jamie.

--

 

[Brendan Reynolds]

I think that's unlikely, Jamie. I don't see anything in the original post
that implies that type of relationship. But perhaps Dean (the original
poster) might be kind enough to clarify the question for us, and then we
won't have to speculate.

 

[Tony Toews]

otherwise someone like me may feel compelled to point out
that your autonumber 'primary key' will fail in its job to provide
effective protection from duplicates.

But autonumber primary keys within Jet are unique. Although Jet
creates them as Indexed - Yes (No Duplicates).

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Jamie, if you'll look again at the original post, this is an ADP. JET is not
involved.

And I missed that detail as well. Thanks for pointing that out.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Jamie Collins]

But autonumber primary keys within Jet are unique.

Consider this simple example:

CREATE TABLE Books (
ISBN CHAR(10) NOT NULL
);

INSERT INTO Books (ISBN) VALUES ('0120887975')
;
INSERT INTO Books (ISBN) VALUES ('0120887975')
;

Can we agree that the above represents a duplication?

If so, how would adding an autonumber PRIMARY KEY, without additionally
constraining the natural key as UNIQUE, help eliminate the duplication?

CREATE TABLE Books (
ID IDENTITY(1, 1) NOT NULL PRIMARY KEY,
ISBN CHAR(10) NOT NULL
);

INSERT INTO Books (ISBN) VALUES ('0120887975')
;
INSERT INTO Books (ISBN) VALUES ('0120887975')
;

The answer is, the autonumber PRIMARY KEY does not prevent the
duplication. Worse, such a PRIMARY KEY may engender a false sense of
security.

Jamie.

--

 

[Tony Toews]

Consider this simple example:

If so, how would adding an autonumber PRIMARY KEY, without additionally
constraining the natural key as UNIQUE, help eliminate the duplication?

Ah, yes, I hadn't thoroughly read your answer so I see your point.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm