Simple Join producing intermittent CASADING DELETES.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Simple Join producing intermittent CASADING DELETES.

Hybrid View

  1. #1
    David Guest

    Simple Join producing intermittent CASADING DELETES.


    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).


  2. #2
    Paul Mc Guest

    Re: Simple Join producing intermittent CASADING DELETES.


    G'day David.

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


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


    >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
    >


    >I see that a delete is being attempted first on the City table (as if >CASCADING

    deletes is enabled).


    Seems to me that cascading is not the issue, it is the Delete statement being
    run against a view. View updateability is a complicated issue, and although
    you may expect the Persons table to be the update target, it may be that
    City is fulfilling that role - ie it is attemtping to Delet from City, not
    Persons.

    It is generally a better idea to delete straight out of a table rather than
    a view - eg:

    Delete Persons From Persons LEFT OUTER JOIN
    City ON Persons.fkCity_id = City.id
    WHERE <some condition>

    This statement if *far* less ambiguous than: Delete From vwMyView.

    HTH,
    Cheers,
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center