It is possible to create CASCADE DELETE or UPDATE actions on certain relationships in Access which can not be constructed on SQL Server. If you encounter problems with this type of relationship, then you will need to remove the CASCADE ACTION and then find an alternative solution to the problem.

Example Problem ..may cause cycles or multiple cascade paths..

In the Access database diagram shown below, all the relationships have cascade update and delete operations activated. The idea being that if a portfolio is deleted the all associated clients, brokers and contracts get deleted. This could be considered as a final step when archiving data out of the system.

If you try and create these relationships in SQL Server, you will get the following error message:-

Introducing FOREIGN KEY constraint 'reltblClienttblPortfolio' on table 'tblClient'
may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION,
or modify other FOREIGN KEY constraints

Solutions

In this case the solution would be to remove the ON DELETE and ON UPDATE actions from the relationships and write a stored procedure to systematically purge the data from the appropriate tables.

This would need to find all clients in a portfolio and then delete all the contracts, and then all the clients. The next step would be to find all brokers in the portfolio and delete all associated contracts and then all the brokers.

It would be assumed here that the application would ensure that contracts only exist for clients and brokers in the same portfolio.

Another alternative to writing a stored procedure would be to write Triggers on the tables to implement the cascade operations.

This is an area in SQL Server where newsgroups and others have discussed whether the product will one day change to provide increased flexibility in handling these problems.

See also support.microsoft.com/kb/321843 (Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths) for more information on this problem.

Top of Page

Download Code

The sample code can be downloaded from here Access 2000 Database.

MUST+SQL automatically removes problems with cascades in relationships removing any cascade operations but leaving the relationships active. Click below to improve your development productivity.

Top of Page