How to enforce cascade update/delete?

C

Chris Burnette

I have an MSDE database with an Access front-end. I have 4 tables in this
DB; one parent and three child tables. The PK of the parent table is used as
a Foreign Key in each of the child tables, and enforce referential integrity
is checked as well as cascade update and delete.

The problem is that when I go to create a new record in the Access front-end
(which uses an Autonumber data type for the parent table PK), the data does
not appear in any of the other tables. This is a problem because I need to
have the same # of records in all 4 tables, and when I create a new record I
need one field (in this case the parent table PK) to be the same for all the
tables so that I can do joins.

Does anyone have any idea why the updates are not cascading? Any help would
be appreciated.

Thanks,

Chris
 
D

Douglas J. Steele

Unfortunately, you've misinterpretted what cascade update is.

Cascade update means that any time you change the primary key of a record in
the primary table, Microsoft Access automatically updates the primary key to
the new value in all related records. For example, if you change a
customer's ID in the Customers table, the CustomerID field in the Orders
table is automatically updated for every one of that customer's orders so
that the relationship isn't broken. Microsoft Access cascades updates
without displaying any message.

It's generally not considered to be a good idea to create dummy records as
place holders. Once you have values to store in the child tables, then
create the records, not before.
 
V

Van T. Dinh

Cascade Update means that if you change the value of the PK, the database
engine will automatically change the corresponding FK to the new value also
so that you don't lose the link between the Parent Record and the Child
Records.

Since you use Identity Field (equivalent to AutoNumber Field in JET), you
cannot change the value of the PK anyway.

Cascade update does not mean creating the Child Records automatically when
the Parent Record is created. There is no way that the database engine
knows how many Child Records you need (One-to-*Many* relationship) and other
details for the Child Records.

When you use Access Form and Subform with appropriate LinkMasterFields /
LinkChildFields, Access will enter the appropriate value for the FK for the
new Child Record in the Subform from the PK value of the Parent Record
currently on the main Form.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top