Deleting rows with a foreign key


I need to delete a row from a table. The table has a foreign key and returns the following error when I try and delete a row.

The DELETE statement conflicted with the REFERENCE constraint "FK_Executed_Parameters_Executed_Reports". The conflict occurred in database "McKesson_Adhoc", table "Audit.Executed_Parameters", column 'Exec_ID'.

I can delete rows by deleting items one by one, for example:

delete from Audit.Executed_Parameters
where exec_id = 2

delete from Audit.Executed_Reports
where exec_id = 2

I have seen the word CASCADE used in articles. How can I use this function?
Mr_ShawAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AbarajCommented:
For use delete cascade use following Create table script...

CREATE TABLE test_child
(SUB1 INT,
[NO] int,
FOREIGN KEY ([NO]) REFERENCES Test_parent
ON DELETE CASCADE)
0
Mr_ShawAuthor Commented:
how do I use it.

Do I USE the delete function normally. Which Table do I delete from etc?
0
AbarajCommented:
In the cascading delete, if we delete a record in the parent table,  corresponding records in child table are automatically deleted.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Mr_ShawAuthor Commented:
My tables are all built can I change the CASCADE property now?
0
AbarajCommented:
then try this procedure
0
AbarajCommented:
Assumption:
--All tables' primary keys are single keys of type int (unless the last table, such as an associative entity, that does not then cascade further)

CREATE PROCEDURE spCascadeDeleteLong
(
-- this cascade deleter uses temporary tables to avoid deep nesting issues
-- requires that tables have one primary key, and its type is int
@cTableName varchar(75), -- name of the table where rows are to be deleted
@cCriteria varchar(255), -- criteria used to delete the rows required
@bDeleteTopHierarchy int, -- delete top hierarchy?
@iLevel int = 0 -- execution level (do not pass this argument)
)
AS
set nocount on
declare @cTab varchar(255), -- name of the child table
@cCol varchar(255), -- name of the linking field on the child table
@cRefTab varchar(255), -- name of the parent table
@cRefCol varchar(255), -- name of the linking field in the parent table
@cSQL varchar(255), -- query string passed to the sp_ExecuteSQL procedure
@cChildCriteria varchar(255), -- criteria to be used to delete records from the child table
@iLevelNew int -- new level (for recursive calling)

-- prepare the temporary table holding the pk values of the called level
IF @iLevel = 0
BEGIN
-- build the temporary table
Create Table #tblCascadeDelete (CallLevel int NOT NULL, PKValue int NOT NULL)
END


-- declare the cursor containing the foreign key constraint information
DECLARE cFKey CURSOR LOCAL FOR
SELECT SO1.name AS Tab, SC1.name AS Col, SO2.name AS RefTab, SC2.name AS RefCol
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol

-- if an initial fetch was successful, then add the appropriate PK values to the temporary table
IF @@FETCH_STATUS = 0
BEGIN
SET @cSQL = 'INSERT INTO #tblCascadeDelete ( CallLevel, PKValue ) SELECT ' + Convert(varchar(3), @iLevel) + ' As CallLevel, [' + @cRefCol + '] As PKValue FROM [' + @cTableName + '] WHERE ' + @cCriteria
EXEC (@cSQL)
END

-- only recurse if rows inserted
IF @@RowCount > 0
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
-- build the criteria to pass on for the next table
SET @cChildCriteria = '[' + @cCol + '] IN (SELECT [PKValue] FROM #tblCascadeDelete Where [CallLevel] = ' + Convert(varchar(3), @iLevel) + ')'

-- call this procedure to delete the child rows
SET @iLevelNew = @iLevel + 1
EXEC spCascadeDeleteLong @cTab, @cChildCriteria, 1, @iLevelNew

-- return the next values
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol
END
END

CLOSE cFKey
DEALLOCATE cFKey

-- delete the rows from this table
IF @bDeleteTopHierarchy <> 0
BEGIN
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
EXEC (@cSQL)
END

-- drop the temporary table if top level, otherwise remove values from this level to allow reuse
IF @iLevel = 0
BEGIN
Drop Table #tblCascadeDelete
END
ELSE
BEGIN
SET @cSQL = 'DELETE FROM #tblCascadeDelete WHERE CallLevel = ' + Convert(varchar(3), @iLevel)
EXEC (@cSQL)
END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mr_ShawAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.