I am using MS Access 2000 as a frontend and MS SQL Server 2000 as a backend.
But, when I eliminate MS Access and use SQL Server Views, I still get unexpected
results.

When I attempt to delete a row from the query, the delete fails with a COLUMN
REFERENCE

constraint error. It appears that a cascading delete is being attempted
even though cascading

delete is not on for the relationship.

To troubleshoot and explain the problem, I created two simple tables and
created a relationship

between them as follows:

In SQL:
------------
CREATE TABLE [dbo].[_City] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[CityName] [varchar] (50) NOT NULL)

CREATE TABLE [dbo].[_Persons] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[PersonName] [varchar] (50) NOT NULL ,
[fkCity_id] [int] NOT NULL)

ALTER TABLE [dbo].[_Persons] ADD
CONSTRAINT [FK__Persons__City] FOREIGN KEY
(
[fkCity_id]
) REFERENCES [dbo].[_City] (
[id]
) ON UPDATE CASCADE

So, we have two tables, Persons and City, related in that each person lives
in a city. The

relationship enforces referential integrity with cascading updates, but NOT
cascading deletes.

Persons
--------
id Primary Key
PersonName varchar 50
fkCity_ID fk to the City table

City
--------
id Primary Key
CityName varchar 50


Here is what the JOIN (view) looks like:

SELECT dbo._Persons.id, dbo._Persons.PersonName, dbo._Persons.fkCity_id,
dbo._City.CityName
FROM dbo._Persons LEFT OUTER JOIN
dbo._City ON dbo._Persons.fkCity_id = dbo._City.id


If I created the join in an Access Query, the behavior is intermittant.
Sometimes, I'm able to

delete and other times I get the constraint violation. When I create the
join in a SQL Server

2000 view, I seem to always get the constraint violation. I have not been
able to determine why

the activity is intermittant in MS Access.

I also traced the SQL Server activity using MS Profiler, and when the delete
fails, I see that a

delete is being attempted first on the City table (as if CASCADING deletes
is enabled).