locked
stored procedure for deleting a record with contstraints (m:n) RRS feed

  • Question

  • I can not get this stored procedure to delete my records...

     

    I have a

    contact table

    RecordID

    FirstName

    LastName

    etc

     

    and a Address table

     

    RecordID

    Street

    Zip

    Town

    Country

     

    And a Relation table

     

    RecordID

    ContactID

    AddressID

    CreateDate

     

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[bc_Contact_Delete]

    @ContactID int

    AS

    --SET NOCOUNT ON

    BEGIN TRY

    BEGIN TRANSACTION -- Start the transaction

    -- Delete all Adresses

    DELETE FROM [Address]

    WHERE

    RecordId in (SELECT ca.AdressId from [ContactAddress] ca

    where

    ca.ContactID = @ContactID)

    -- Delete all Relations

    DELETE FROM [ContactAdress]

    WHERE ContactID = @ContactID

    --- Delete Kontakt

    DELETE FROM [Contact] WHERE (([RecordId] = @ContactID))

    COMMIT TRANSACTION

    END TRY

    BEGIN CATCH

    -- Whoops, there was an error

    ROLLBACK TRANSACTION

    -- Raise an error with the

    -- details of the exception

    DECLARE @ErrMsg nvarchar(4000),

    @ErrSeverity int

    SELECT @ErrMsg = ERROR_MESSAGE(),

    @ErrSeverity = ERROR_SEVERITY()

    RAISERROR(@ErrMsg, @ErrSeverity, 1)

    END CATCH

    RETURN

     

     

    My Errormessage is

    The DELETE statement conflicted with the REFERENCE constraint "FK_bc_ContactAdress_bc_Address". The conflict occurred in database "bContacts", table "dbo.ContactAddress", column 'AdressID'.

     

    Can someone please post me an advice?

     

    Tuesday, October 9, 2007 2:42 PM

Answers

  • Since you seem to be using SQL 2005, you can also use the new OUTPUT clause from the delete to capture the address ids you need to delete in a table variable.

     

     

    Code Block

    DECLARE @AddressIDs TABLE (AddressID int)

     

    DELETE

    FROM ContactAddress

    OUTPUT deleted.AddressID INTO @AddressIDs

    WHERE ContactID = @ContactID

     

    DELETE FROM Address

    WHERE RecordID IN (SELECT * FROM @AddressIDs)

     

     

    Wednesday, October 10, 2007 9:50 PM

All replies

  • It appears that you rightfully have foreign key constrants between the address and contact address tables.  Under those circumstances, a statement such as:

     

    Code Block

    DELETE FROM [Address]

    WHERE

    RecordId in (SELECT ca.AdressId from [ContactAddress] ca

    where

    ca.ContactID = @ContactID)

     

     

    cannot possibly work unless you have specified address table for cascading delete.  Give a look to article "Cascading Referential Integrity Constraints" in books online.  Pay attention to the ON DELETE CASCADE portion -- you are going to need to use cascading delete to run a statement such as this.

     

    An alternative is to (1) fetch the address ID from the ContactAddress table using the @ContactID to match the ContactID column, (2) delete the ContactAddress record that matches on ContactID = @ContactID and then (3) delete the address table record based on the address ID you fetch from the first step.  This would be my preferred method for this particular problem.  Turning on cascading delete may cause records to be deleted by some other process that should not have otherwise been deleted.  You might be able to just re-order your steps a bit to get this to work correctly.  Maybe transform your first two deletes into something more like:

     

    Code Block

    -- Select the ADDRESS ID
    DECLARE @AddressID Int
    SELECT @AddressID = AddressID
    FROM Address
    WHERE ContactID = @ContactID
    -- DELETE all Addresses
    DELETE FROM [Address]
    WHERE AddressID = @AddressID
    -- Delete all Relations
    DELETE FROM [ContactAdress]
    WHERE ContactID = @ContactID

     

     

    Tuesday, October 9, 2007 2:48 PM
  • thanks for your reponse ...

     

    the reason for the statement you quoted above is that I possible have multiple addresses for one contact.

    That is why I choosed the "delete where in" statement.

     

    How would your code address the the fact that I could have more AddressID's?

    Is there a type for variables that could store more int (like an array)? which I can pass over to a "delete where in"-statement?

     

    I did not enabled cascading delete for the same reason you mentioned above... I would like to have control

     

    Tuesday, October 9, 2007 4:07 PM
  • Good point; insert the data into a table variable and then try to DELETE .. WHERE EXISTS or maybe with a join; perhaps like:

     

    Code Block

    -- Select the ADDRESS ID
    DECLARE @AddressList table (AddressID Int)
    INSERT INTO @AddressList
    SELECT AddressID
    FROM Address
    WHERE ContactID = @ContactID


    -- DELETE all Addresses
    DELETE FROM [Address]
    FROM @AddressList a
    JOIN [Address] b
    ON a.AddressID = b.AddressID

     

     

    Tuesday, October 9, 2007 4:32 PM
  • Since you seem to be using SQL 2005, you can also use the new OUTPUT clause from the delete to capture the address ids you need to delete in a table variable.

     

     

    Code Block

    DECLARE @AddressIDs TABLE (AddressID int)

     

    DELETE

    FROM ContactAddress

    OUTPUT deleted.AddressID INTO @AddressIDs

    WHERE ContactID = @ContactID

     

    DELETE FROM Address

    WHERE RecordID IN (SELECT * FROM @AddressIDs)

     

     

    Wednesday, October 10, 2007 9:50 PM