Cascade Delete

[AndeanWayne]

I have a database with two related tables based on a property ID code. I want to have a cascade delete so when I delete a record in the property info table the related record in the investment table is deleted. The rpobelem is this is a one to one relationship because the PropertyID is the Primary Key for both tables. Since records in the Investment table are created when the Property Info table record is created I thought to eliminate the Primary Key from the Investment table. Voila! The relationship between the two tables is now a one to many. But, imagine my chagrin when the Cascade Delete Related check box is dulled out and cannot be selected. Can a one to one relationship have a cascade delete? Why didn't my solution work? Though the relationship says one to many the line connecting the tables does not show that. HELP!

 

[Cronk]

For a start, if the relationship is one to one, why have two tables?

Secondly if you eliminate PropertyID from the second table, how does Access know which records to delete in your desired cascade delete?

 

[CJ_London]

For cascade delete to work you have to have a one to many relationship from the primary key in one table (PK) to another field (FK) in another table, and the other table also has to have a primary key.

Using an autonumber as your FK (Family Key) is extremely dangerous, if one record is deleted you will be out of sync. In addition autonumber only means it will generate a unique key, it does not mean it will necessarily be sequential - in fact you can set it to be random which was primarily used for syncing between databases.

In your second table I would change your ID field to type long and its name to FK or similar (you will probably need to delete it and recreate it since once a table has data entered you cannot change an autonumber to another type). Then add a new ID field of type autonumber and make that the primary key

 

[Cronk]

CJ

I disagree. You can have cascade deletes if the common field is the primary key in both table with the relationship one to one and referential integrity with such delete is set in relationships.

But it's more likely inappropriate table design. The only reason for having a one to one relationship (PK or FK) is the total number of fields for an entity is greater than 255. In 30 years of working with databases, I have not seen a need for this in a properly constructed relational database.

With all the fields in one table, cascade deletes would then not be an issue.

 

[CJ_London]

@Cronk

You can have cascade deletes if the common field is the primary key in both table

After investigation - if I try to create such a relationship I get an 'invalid field definition error in definition' but then realised I had also ticked the cascade updates option as well! So I stand corrected.

 

[Cronk]

CJ, it happens that we make incorrect calls at times, me too often, for whatever reasons.

From what I've read, I consider you very a knowledgeable contributor to these forums and the issue is not to focus on the incorrect comments but to improve all of our collective understandings.

 

[CJ_London]

@Cronk, I agree, just thought the OP would like an explanation