delete cascade for 2 tables?

[bonneylake]

Hey Everyone,

I was wondering if anyone could provide a tutorial or example on how to create a stored procedure that uses delete cascade to delete records from 2 tables? i have seen examples online, but the ones i found online only show examples for if both tables have a field in common. My 2 tables have nothing in common with each other. An both are primary keys in there own table.Right now here is what i have for delete.I am using SQL Server 2005.

set ANSI_NULLS ON
 set QUOTED_IDENTIFIER ON
 go


 -- =============================================
. -- Author:        <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[usp_CS_Deletesp]
 -- Add the parameters for the stored procedure here
(
 @pkb_fk_ticketNo nvarchar(100),
@fk_ticketNo nvarchar(100)) AS

DELETE FROM tbl_CS_serial
WHERE pkb_fk_ticketNo=@pkb_fk_ticketNo

DELETE FROM tbl_CS_parts
 WHERE fk_ticketNo=@fk_ticketNo

Thank you in advance,
Rach

[acoder]

Are you sure that they have nothing in common? If many parts belong to a serial then surely you'd have a foreign key that links the two?

[bonneylake]

Are you sure that they have nothing in common? If many parts belong to a serial then surely you'd have a foreign key that links the two?

Hey Acoder,

Well the thing is they don't have a relationship together. There both completely seperated.. But they both do hold the same number. So if pkb_fk_ticketNo has the value of 1 so will fk_ticketNo . But although they hold the same value they don't have the same field name in common. Like in the examples i saw one example they showed was both tables had the field name ID. However, mine are not like that they have 2 different names.An i know that both fields don't have to have the same name to have a relationship. But i know for a fact these fields don't share a relationship. Because if i go to insert my form an fk_ticketNo don't work right, pkb_fk_ticketNo will still work right with or without fk_ticketNo.I hope that makes since, its hard to explain since i am not the one that created the tables myself.

Thank you,
Rach

[acoder]

If I understand correctly, a ticket has many serials and each serial has many parts. If so, then parts should not have the foreign key pointing to ticket, it should be for serial. Do you have a field in parts containing the foreign key that matches the primary key of serial?

[bonneylake]

If I understand correctly, a ticket has many serials and each serial has many parts. If so, then parts should not have the foreign key pointing to ticket, it should be for serial. Do you have a field in parts containing the foreign key that matches the primary key of serial?

Hey Acoder,

i really do not know.I know that fk_serialNo in parts table holds the same value as pka_serialNo in the serial table. An fk_ticketNo in parts table holds the same value as pkb_fk_ticketNo in the serial table. An when i say same value i mean when i insert it holds the same number. but here are the tables an they fields they have in case i am understanding wrong

parts table

pk_partId (primary key)
fk_serialNo
fk_ticketNo
hc_partNo
part_returned
rma_number
defective
submission

serial table

pka_serialNo(primary key)
pkb_fk_ticketNo(primary key)
model_no
product_type
software_hardware
resolution
resolution_date
verification_date
rma_data
type_hardware_failure
dept_responsibility
resoulution_verified_by

Thank you,
Rach

[acoder]

Based on the naming, it seems that you have two primary keys in the serial table. In the parts table you have two foreign keys that correspond to these primary keys.

Now, if pka_serialNo is unique, I don't see any need for pkb_fk_ticketNo being a primary key. In fact, it should be a foreign key to the ticket table.

If this is all correct, the constraint should have fk_serialNo as a foreign key referencing pka_serialNo in the serial table.

[bonneylake]

Based on the naming, it seems that you have two primary keys in the serial table. In the parts table you have two foreign keys that correspond to these primary keys.

Now, if pka_serialNo is unique, I don't see any need for pkb_fk_ticketNo being a primary key. In fact, it should be a foreign key to the ticket table.

If this is all correct, the constraint should have fk_serialNo as a foreign key referencing pka_serialNo in the serial table.

Hey Acoder,

I know the ones that have fk in the name of the field are suppose to be foreign keys, or atleast that is what i was told.an to be honest with you i am afraid to change anything on account of the other developer made it an i know he understands how it needs to work better then i do.

I think the only reason pkb_fk_ticketNo is a primary key is to make sure the serial and ticket match up. Basically a user can add the same serial in the table, it might be the same day or a few months after. However, a user can not enter the same ticket number in the table. Basically you don't want all serials that have 555 removed, only the one associated with the ticket you are removing or adding from. So i think its suppose to be known as a foreign key, but is set as a primary to avoid problems. I know that don't make a lot of sense, it made more sense when he explained it to me a few months back.But its basically to make sure a serial don't get removed that doesn't need to be removed, only the one associated with the ticket.So i guess pka_serialNo is not unique without the ticket because users can add the same serial number to the table.

Thank you,
Rach

[acoder]

That does seem to make a lot more sense. Now someone else will have to jump in and help on the exact syntax for delete cascade on composite foreign keys and if it's even possible.

[ck9663]

Rach,

First, it does not have to be the same column names for the table to have relationships.

Second, based on the use of these tables, I would say Serial Number and Ticket Number is enough to relate the two.

Third, is this process going to done every time or you're just doing it once, to clean your tables? If the former, you can either create the relationship between the two tables and issue a CASCADE during DELETE or create a TRIGGER to do the cascade for you. If the latter, delete what's in SERIAL first then the PARTS table.

Make sure to backup your db or tables before doing anything.

Happy coding!

-- CK

[bonneylake]

Rach,

First, it does not have to be the same column names for the table to have relationships.

Second, based on the use of these tables, I would say Serial Number and Ticket Number is enough to relate the two.

Third, is this process going to done every time or you're just doing it once, to clean your tables? If the former, you can either create the relationship between the two tables and issue a CASCADE during DELETE or create a TRIGGER to do the cascade for you. If the latter, delete what's in SERIAL first then the PARTS table.

Make sure to backup your db or tables before doing anything.

Happy coding!

-- CK

Hey CK,

Well the process is going to be done everytime the user submits the form. So i am not sure which of your suggestions would be best to go with?

Thank you,
Rach

[ck9663]

What do you want to do with the deleted records? Do you want to keep it for historical/audit purposes? Or completely delete it?

-- CK

[bonneylake]

What do you want to do with the deleted records? Do you want to keep it for historical/audit purposes? Or completely delete it?

-- CK

Hey CK,

Would like to completely delete it, to replace it with new information.

Thank you,
Rach

[ck9663]

If you delete it, you won't be able to recover it other than if you have a backup. If needed, you can just add a column that flags if record is deleted or not.

Answering your question, I think a FOREIGN KEY Constraint is better. Read that here. Then during delete, use CASCADE.

Happy coding.

-- CK

[bonneylake]

If you delete it, you won't be able to recover it other than if you have a backup. If needed, you can just add a column that flags if record is deleted or not.

Answering your question, I think a FOREIGN KEY Constraint is better. Read that here. Then during delete, use CASCADE.

Happy coding.

-- CK

Hey CK,

So basically in the tables i need to put a relationship between pkb_fk_ticketNo in the serial table and fk_ticketNo in the parts table correct?

an then on the other part for the delete i must admit i am completely confused. I
started trying to do it but i have gotten very confused.

For r @iRowsAffected int OUTPUT. I don't know if i need to just put all the rows that appear in the serial table in there followed by OUTPUT behind the last field.

For query string passed to the sp_ExecuteSQL procedure . I have no clue what this would be or if i need it

and For @iChildRows i am not sure if i list all the fields for the parts table.

CREATE Procedure spDeleteRows
/* 
Recursive row delete procedure. 

It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on.  This is designed to do the 
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
table to find any child tables, then deletes the soon-to-be orphan records from them using 
recursive calls to this procedure. Once all child records are gone, the rows are deleted 
from the selected table.   It is designed at this time to be run at the command line. It could 
also be used in code, but the printed output will not be available.
*/
	(
	@dbo.usp_CS_serial, /* name of the table where rows are to be deleted */
	@pka_fk_ticketNo nvarchar(100), /* criteria used to delete the rows required */
	@iRowsAffected int OUTPUT /* number of records affected by the delete */
	)
As
set nocount on
declare 	@dbo.tbl_CS_parts, /* name of the child table */
	@fk_ticketNo nvarchar(100), /* name of the linking field on the child table */
	@dbo.usp_CS_serial, /* name of the parent table */
	@pkb_fk_ticketNo nvarchar(100), /* name of the linking field in the parent table */
	@fk_serialNo nvarchar(100), /* name of the foreign key */
	@cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
	@fk_ticketNo nvarchar(100), /* criteria to be used to delete 
                                           records from the child table */
	@iChildRows int /* number of rows deleted from the child table */

an for the rest i am not sure if i need it all or how much i need of it

/* 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, 
       FO.name AS FKName
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 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
     BEGIN
	/* build the criteria to delete rows from the child table. As it uses the 
           criteria passed to this procedure, it gets progressively larger with 
           recursive calls */
	SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 
                              @cRefTab +'] WHERE ' + @cCriteria + ')'
	print 'Deleting records from table ' + @cTab
	/* call this procedure to delete the child rows */
	EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
	FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
     END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected  */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName

Thank you,
Rach

[bonneylake]

Hey CK,

Ignore the questions above. I messed with it some more an i know what i am confused on a bit better

So basically in the tables i need to put a relationship between pkb_fk_ticketNo in the serial table and fk_ticketNo in the parts table correct?

For @iRowsAffected int OUTPUT, do i put all the fields that appear in serial table or does something else need to be here?

For @iChildRows int, do i need to put all the fields that appear in parts or
does something else need to go here?

the part i am confused on is the below. I am not sure if need declare an really unsure if i need all the innerjoin details.

<!---/* 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, 
       FO.name AS FKName
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 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @dbo.tbl_CS_parts, @fk_ticketNo, @dbo.usp_CS_serial, @pkb_fk_ticketNo, @fk_serialNo
WHILE @@FETCH_STATUS = 0--->

but here is what i have right now in full

CREATE Procedure spDeleteRows
/* 
Recursive row delete procedure. 

It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on.  This is designed to do the 
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
table to find any child tables, then deletes the soon-to-be orphan records from them using 
recursive calls to this procedure. Once all child records are gone, the rows are deleted 
from the selected table.   It is designed at this time to be run at the command line. It could 
also be used in code, but the printed output will not be available.
*/
	(
	@dbo.usp_CS_serial, /* name of the table where rows are to be deleted */
	@pka_fk_ticketNo nvarchar(100), /* criteria used to delete the rows required */
	@iRowsAffected int OUTPUT /* number of records affected by the delete */
	)
As
set nocount on
declare 	@dbo.tbl_CS_parts, /* name of the child table */
	@fk_ticketNo nvarchar(100), /* name of the linking field on the child table */
	@dbo.usp_CS_serial, /* name of the parent table */
	@pkb_fk_ticketNo nvarchar(100), /* name of the linking field in the parent table */
	@fk_serialNo nvarchar(100), /* name of the foreign key */
	@cSQL ,  query string passed to the sp_ExecuteSQL procedure 
	@fk_ticketNo nvarchar(100), /* criteria to be used to delete 
                                           records from the child table */
	@iChildRows int /* number of rows deleted from the child table */

<!---/* 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, 
       FO.name AS FKName
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 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @dbo.tbl_CS_parts, @fk_ticketNo, @dbo.usp_CS_serial, @pkb_fk_ticketNo, @fk_serialNo
WHILE @@FETCH_STATUS = 0--->
     BEGIN
	/* build the criteria to delete rows from the child table. As it uses the 
           criteria passed to this procedure, it gets progressively larger with 
           recursive calls */
	SET @fk_ticketNo = @fk_ticketNo + ' in (SELECT [' + @pkb_fk_ticketNo + '] FROM [' + 
                              @dbo.usp_CS_serial +'] WHERE ' + @pka_fk_ticketNo + ')'
	<!---print 'Deleting records from table ' + @cTab--->
	/* call this procedure to delete the child rows */
	EXEC spDeleteRows @dbo.tbl_CS_parts, @fk_ticketNo, @iChildRows OUTPUT
	FETCH NEXT FROM cFKey INTO @dbo.tbl_CS_parts, @fk_ticketNo, @dbo.usp_CS_serial, @pkb_fk_ticketNo, @fk_serialNo
     END
<!---Close cFKey
DeAllocate cFKey--->
/* finally delete the rows from this table and display the rows affected  */
SET @cSQL = 'DELETE FROM [' + @dbo.usp_CS_serial + '] WHERE ' + @pka_fk_ticketNo
<!---print @cSQL--->
EXEC sp_ExecuteSQL @cSQL
<!---print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @dbo.usp_CS_serial--->

Thank you,
Rach