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
50 5663
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?
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
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?
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
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.
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
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.
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
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
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
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
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
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
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
Then during delete, use CASCADE.
Correct me if I'm wrong, but isn't that for an older version of SQL Server? In newer versions, from 2000 onwards, you can just add ON DELETE CASCADE to the foreign key constraint.
Sorry, I gave you the wrong link. I'm really sorry.
Here's a sample of CASCADE
Consider this sample table: - set nocount on
-
-
create table Authors
-
(AuthorID int not null, AuthorName varchar(30), AuthorCity varchar(15))
-
-
create table Books
-
(aid int not null, BookID int not null, BookTitle varchar(30), BookPrice money)
-
-
insert into Authors values (1, 'Jhon', 'San Francisco')
-
insert into Authors values (2, 'Paul', 'Los Angeles')
-
insert into Authors values (3, 'George', 'San Diego')
-
insert into Authors values (4, 'Ringo', 'Oakland')
-
-
insert into Books values (1, 1, 'Yesterday',5.00)
-
insert into Books values (1, 2, 'In My Life',7.50)
-
insert into Books values (1, 3, 'Hey Junde',4.45)
-
insert into Books values (2, 4, 'Fool On the Hill',NULL)
-
insert into Books values (2, 6, 'If I Fell',7.80)
-
insert into Books values (4, 7, 'Let It Be',NULL)
-
insert into Books values (4, 8, 'Till There Was You',0.00)
-
insert into Books values (2, 9, 'Yellow Submarine',34.65)
-
insert into Books values
-
(1, 10, 'I Should Have Known Better',65.33)
-
-
select * from authors
-
select * from books
-
You can define PK and FK during table creation. Since you have existing tables, I'm going to show you how to create them on existing table. - alter table authors add primary key (authorid)
-
-
alter table books add primary key (aid, bookid)
-
alter table books add constraint fkaid foreign key (aid) references authors(authorid) on delete cascade
Now, issue a delete on the parent table: -
-
delete from Authors where AuthorID = 2
-
-
select * from Authors
-
select * from Books
-
-
Paste the entire code in SQL Analyzer and try it.
-- CK
Hey CK,
its ok :). i am just grateful that was not the one i needed lol. But i do got a few questions left.
In my tables do i need to create the relationship between pkb_fk_ticketNo in
the serial table and fk_ticketNo in the parts table?
an the only part i am confused on is in the example you provided (which thank you for the example). at the part that says constraint fkaid foreign key (aid). they put in the example a foreign key for in the table books.
An i was wondering do i need to do the same? for mine i changed books to the parts table and then i just put fk_ticketNo. do i need to put the other field that i talked about from parts which was fk_serialNo as the foreign key? An then do i need to add a foreign key for the serial table as well? Basically i am just wondering is how i would be able to delete fields based on both the serialNo and the ticketNo from both the parts and serial table? heres an example of what i am trying to say. i know the ticketNo needs to be in both of the tables for altering, just not sure if serialNo needs to be in both of the tables for it to be able to delete based on ticketNo and serialNo. - set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-
-
-
-- =============================================
-
-- Author: <Author,,Name>
-
-- Create date: <Create Date,,>
-
-- Description: <Description,,>
-
-- =============================================
-
ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
-
-- Add the parameters for the stored procedure here
-
-
ALTER TABLE tbl_CS_serial add primary key(pka_serialNo,pkb_fk_ticketNo)
-
ALTER TABLE tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
-
ALTER TABLE tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_serialNo)
-
references tbl_CS_serial(pkb_fk_ticketNo) on delete cascade
-
-
delete from tbl_CS_serial where (pkb_fk_ticketNo = @pkb_fk_ticketNo and fk_ticketNo = @fk_ticketNo)
-
-
select * from tbl_CS_serial
-
select * from tbl_CS_parts
-
here is what i currently have - set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-
-
-
-- =============================================
-
-- Author: <Author,,Name>
-
-- Create date: <Create Date,,>
-
-- Description: <Description,,>
-
-- =============================================
-
ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
-
-- Add the parameters for the stored procedure here
-
-
ALTER TABLE tbl_CS_serial add primary key(pkb_fk_ticketNo)
-
ALTER TABLE tbl_CS_parts add primary key (fk_ticketNo)
-
ALTER TABLE tbl_CS_parts add constraint
-
fkaid foreign key()
-
references tbl_CS_serial(pkb_fk_ticketNo) on delete cascade
-
-
delete from tbl_CS_serial where pkb_fk_ticketNo = @pkb_fk_ticketNo
-
-
select * from tbl_CS_serial
-
select * from tbl_CS_parts
Thank you,
Rach
Hey CK,
This is actually what i currently have, made changes since last post. - set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-
-- =============================================
-
-- Author: <Author,,Name>
-
-- Create date: <Create Date,,>
-
-- Description: <Description,,>
-
-- =============================================
-
CREATE PROCEDURE [dbo].[usp_CS_Deleteserialparts]
-
-- Add the parameters for the stored procedure here
-
(
-
@pkb_fk_ticketNo nvarchar(100),
-
@fk_ticketNo nvarchar(100)) AS
-
-
ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_serialNo)
-
references dbo.tbl_CS_serial(pkb_fk_ticketNo) on delete cascade
-
-
delete from dbo.tbl_CS_serial where pkb_fk_ticketNo = @pkb_fk_ticketNo
-
-
select * from dbo.tbl_CS_serial
-
select * from dbo.tbl_CS_parts
Thank you,
Rach
If you already have the primary keys set up, I don't think you'd need to set them again.
You'll only need to add this constraint once, so it wouldn't make sense to combine all of this into a stored procedure. Also, the foreign key is composite, so it should have both fields together and reference both fields in the serial table.
If you already have the primary keys set up, I don't think you'd need to set them again.
You'll only need to add this constraint once, so it wouldn't make sense to combine all of this into a stored procedure. Also, the foreign key is composite, so it should have both fields together and reference both fields in the serial table.
Hey Acoder,
The reason i set up them is because every time i tried to run it it kept saying i needed to declare it. I think its because of at where i have @pkb_fk_ticketNo. But for some reason when i set it i didn't get any errors.
But are you saying with the constraint part that i don't need to create a stored procedure or that i don't need as much information in one stored procedure? An with the foreign key composite are you saying something like this? -
set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-
-- =============================================
-
-- Author: <Author,,Name>
-
-- Create date: <Create Date,,>
-
-- Description: <Description,,>
-
-- =============================================
-
CREATE PROCEDURE [dbo].[usp_CS_Deleteserialparts]
-
-- Add the parameters for the stored procedure here
-
(
-
@pkb_fk_ticketNo nvarchar(100),
-
@fk_ticketNo nvarchar(100)) AS
-
-
ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo,pka_serialNo)
-
ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_serialNo,fk_ticketNo)
-
references dbo.tbl_CS_serial(pkb_fk_ticketNo,pka_serialNo) on delete cascade
-
-
delete from dbo.tbl_CS_serial where (pkb_fk_ticketNo = @pkb_fk_ticketNo
-
and fk_ticketNo = @fk_ticketNo)
-
-
select * from dbo.tbl_CS_serial
-
select * from dbo.tbl_CS_parts
Thank you,
Rach
I'm not an expert in SQL Server, so I'm going on my experience with other databases. I would think you'd need something like: - ALTER TABLE dbo.tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
-
references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
Now, once this is set up, whenever you delete from the serial table, you don't have to worry about child records in the parts table - they would be deleted automatically.
I'm not an expert in SQL Server, so I'm going on my experience with other databases. I would think you'd need something like: - ALTER TABLE dbo.tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
-
references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
Now, once this is set up, whenever you delete from the serial table, you don't have to worry about child records in the parts table - they would be deleted automatically.
Hey Acoder,
Ok this is what i got for it.Would this be correct? i didn't get any errors. But i still have to set 2 fields at the top. An its because when i do the where an i do the field= @, it complains about the @ saying i didn't declare it. But here is what i have. - set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-
-
-- =============================================
-
-- Author: <Author,,Name>
-
-- Create date: <Create Date,,>
-
-- Description: <Description,,>
-
-- =============================================
-
ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
-
-- Add the parameters for the stored procedure here
-
(
-
@pkb_fk_ticketNo nvarchar(100),
-
@pka_serialNo nvarchar(100)) AS
-
-
ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
-
references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
-
-
delete from dbo.tbl_CS_serial where (pkb_fk_ticketNo = @pkb_fk_ticketNo
-
and pka_serialNo = @pka_serialNo)
-
-
select * from dbo.tbl_CS_serial
-
select * from dbo.tbl_CS_parts
Thank you,
Rach
You don't need to set the primary keys and foreign key constraints each time you delete. Try it in the SQL Query Analyzer. You also don't need the select statements. That was only for a test. If you try the example code by CK, you'll have an idea how this is working. In your stored procedure, you would only need the delete statement.
You don't need to set the primary keys and foreign key constraints each time you delete. Try it in the SQL Query Analyzer. You also don't need the select statements. That was only for a test. If you try the example code by CK, you'll have an idea how this is working. In your stored procedure, you would only need the delete statement.
Hey Acoder,
Ok for the primary keys. Basically just take off add primary key off of the first 2 lines that say alter table?
an your saying basically i don't need - where (pkb_fk_ticketNo = @pkb_fk_ticketNo
-
and pka_serialNo = @pka_serialNo)
and because i don't need that i do not need to set the primary keys correct?
so it would be like so correct? - set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-
-
-- =============================================
-
-- Author: <Author,,Name>
-
-- Create date: <Create Date,,>
-
-- Description: <Description,,>
-
-- =============================================
-
ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
-
-- Add the parameters for the stored procedure here
-
-
-
ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
-
references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
-
-
delete from dbo.tbl_CS_serial
Thank you,
Rach
You would need the where clause and the parameters too. What you don't need is the ALTER statements. Well, you do need them, but not in the stored procedure. You need to use them somewhere though. Try the ALTER statements in the Query Analyzer.
Note: I'm making a few assumptions here. Hopefully, someone can fill in the gaps if I've missed something.
You would need the where clause and the parameters too. What you don't need is the ALTER statements. Well, you do need them, but not in the stored procedure. You need to use them somewhere though. Try the ALTER statements in the Query Analyzer.
Note: I'm making a few assumptions here. Hopefully, someone can fill in the gaps if I've missed something.
Hey Acoder,
When i tried the following lines in the query analyzer it says. the first line already has a primary key defined on it. then it says could not create constraint see previously errors. - ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
-
references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
-
-
delete from dbo.tbl_CS_serial
Thank you,
Rach
That makes sense (the primary key part) because you should already have primary keys set up, so those statements are not required. However, I'm not sure about the foreign key constraint. Do you have one already set up for the parts table?
That makes sense (the primary key part) because you should already have primary keys set up, so those statements are not required. However, I'm not sure about the foreign key constraint. Do you have one already set up for the parts table?
Hey Acoder,
The only thing i haven't done is i havent made the tables do the relationship between the serial and parts table, not sure if that could be messing with the foreign key contraint. But i believe the fk_serialNo in the parts table suppose to be the foreign key or would be the foreign key. But so would i get rid of the following 2 lines? - ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
-
ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
an then it should be in total (what i am trying in the query analyzer) - ALTER TABLE dbo.tbl_CS_parts add constraint
-
fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
-
references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
-
-
delete from dbo.tbl_CS_serial
Thank you,
Rach
You don't need line 5. I'm not sure exactly what "see previous errors" would mean, but you could try "with nocheck". This would avoid checking when creating the foreign key constraint. There could be problems later on though. Are you sure that all records would meet this constraint?
Hey Acoder,
Well this seemed to work with no errors. Would this be ok to use? An all should meet the constraint. Because they will have to fill out a serial (especially certain fields or else errors pop up) but they won't always have to fill in a part. But a serial yes. - ALTER TABLE dbo.tbl_CS_parts with nocheck add constraint
-
fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
-
references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
an well since you believe it could cause problems, so i tried this an got the error
Introducing FOREIGN KEY constraint 'fk_serialNo' on table 'tbl_CS_parts' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.an then below it says could not create constraint. see previous errors. - ALTER TABLE dbo.tbl_CS_parts add constraint
-
fk_serialNo foreign key(fk_ticketNo, fk_serialNo)
-
references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
-
Thank you,
Rach
Would you mind posting the actual structure (like a create table) and some sample data? We'll try and built it for you.
-- CK
Would you mind posting the actual structure (like a create table) and some sample data? We'll try and built it for you.
-- CK
Hey CK,
Well i can show you what fields are in the table, an can give an example of a record/records that appear but i cant show a create table since its already created an i didn't create it. But here is what i have
In the serial table, these are all the fields - @pka_serialNo nvarchar(100),
-
@pkb_fk_ticketNo nvarchar(100),
-
@model_no nvarchar(50),
-
@product_type nvarchar(100),
-
@software_hardware nvarchar(40),
-
@resolution nvarchar(500),
-
@resolution_date datetime,
-
@resolution_verified_by nvarchar(50),
-
@verification_date datetime,
-
@dept_responsibility nvarchar(50),
-
@type_hardware_failure nvarchar(100)
In the parts table, these are all the fields - @fk_serialNo nvarchar(100),
-
@fk_ticketNo nvarchar(100),
-
@hc_partNo nvarchar(50),
-
@part_returned char(10),
-
@defective bit,
-
@rma_number nvarchar(50)
here is the information i put for each field in serial - pka_serialNo = 323
-
pkb_fk_ticketNo = 444
-
model_no = 340LPB
-
product_type = Desktop
-
software_hardware = Nic
-
resolution = testres
-
resolution_date = 10/30/2008
-
resolution_verified_by = Darrell McCullum
-
verification_date = 10/30/2008
-
dept_responsibility = Unknown at this time
-
type_hardware_failure = DOA
here is the information i put for each field in parts - fk_serialNo = 323
-
fk_ticketNo = 444
-
hc_partNo = part2
-
part_returned = 1
-
defective = 1
-
rma_number = dasfda
-
Thank you,
Rach
What's the relationship between these tables?
In my sample, it's one-to-many. One row in Authors could have one or more rows in Books.
Here's the content: - select * from Authors
-
-
select * from Books
-
-
AuthorID AuthorName AuthorCity
-
----------- ------------------------------ ---------------
-
1 Jhon San Francisco
-
3 George San Diego
-
4 Ringo Oakland
-
-
aid BookID BookTitle BookPrice
-
----------- ----------- ------------------------------ ---------------------
-
1 1 Yesterday 5.00
-
1 2 In My Life 7.50
-
1 3 Hey Junde 4.45
-
1 10 I Should Have Known Better 65.33
-
4 7 Let It Be NULL
-
4 8 Till There Was You 0.00
Your structure seems to be one-to-one. I can't seem to figure out which one is the parent and the child.
Could you post some of your data that way? We can also figure out the relationship between these two.
-- CK
Hey CK,
well the thing is the only thing they both have in common in the serialNo and the ticketNo. Beyond that they hold 2 completely different sets of information. Currently both the serial and parts do not have a relationship and have been kept separate the whole time. But i would think the serial table could/would be the parent table. Basically when a user fills out the form they fill out the serial table first, an then if they need to they fill out the parts table. - select * from dbo.tbl_CS_serial
-
-
select * from dbo.tbl_CS_parts
this is serial table information, i couldn't fit all the fields on the same line as your example so hope thats ok -
pka_serialNo (primary key)
-
------------------
-
323
-
-
-
pkb_fk_ticketNo (primary key)
-
-----------------
-
444
-
-
-
model_no
-
----------------------
-
340LPB
-
-
-
product_type
-
---------------------
-
Desktop
-
-
-
software_hardware
-
-----------------------
-
Nic
-
-
-
resolution
-
--------------
-
testres
-
-
-
resolution_date
-
------------------
-
10/30/2008
-
-
-
verification_date
-
----------------------
-
10/30/2008
-
-
-
type_hardware_failure
-
-----------------------
-
DOA
-
-
-
dept_responsibility
-
------------------------------
-
Unknown at this time
-
-
-
resolution_verified_by
-
------------------------------
-
Darrell McCullum
-
an this is the parts table -
-
fk_serialNo
-
--------------
-
323
-
-
-
fk_ticketNo
-
----------------
-
444
-
-
-
hc_partNo
-
--------------
-
part2
-
-
-
part_returned
-
------------------
-
1
-
-
-
rma_number
-
------------------
-
dasfda
-
-
-
defective
-
-------------
-
true
-
-
-
-
Thank you,
Rach
Here's a sample data of your table : -
select * from serial
-
select * from parts
-
-
pka_serialNo pkb_fk_ticketNo model_no product_type software_hardware resolution resolution_date resolution_verified_by verification_date dept_responsibility type_hardware_failure
-
------------ --------------- ---------- -------------------- -------------------- -------------------- ----------------------- ---------------------- ----------------------- -------------------- ---------------------
-
1 1 340LPB Desktop Nic testres 2008-10-30 00:00:00.000 Darrell McCullum 2008-10-30 00:00:00.000 Unknown at this time DOA
-
2 2 340LPB Desktop Nic testres 2008-10-30 00:00:00.000 Darrell McCullum 2008-10-30 00:00:00.000 Unknown at this time DOA
-
3 3 340LPB Desktop Nic testres 2008-10-30 00:00:00.000 Darrell McCullum 2008-10-30 00:00:00.000 Unknown at this time DOA
-
4 4 340LPB Desktop Nic testres 2008-10-30 00:00:00.000 Darrell McCullum 2008-10-30 00:00:00.000 Unknown at this time DOA
-
-
fk_serialNo fk_ticketNo hc_partNo part_returned defective rma_number
-
----------- ----------- -------------------- ------------- --------- --------------------
-
3 3 part2 1 1 dasfda
-
1 1 part2 1 1 dasfda
-
2 2 part2 1 1 dasfda
-
4 4 part2 1 1 dasfda
-
-
Now, let's create the PRIMARY and FOREIGN KEYS. - alter table serial
-
add constraint pkserial PRIMARY KEY (pka_serialNo,pkb_fk_ticketNo)
-
-
alter table parts
-
add constraint fkserial FOREIGN KEY (fk_serialNo,fk_ticketNo) REFERENCES serial (pka_serialNo,pkb_fk_ticketNo) on delete cascade
-
If they're already existing, it will show an error. However, watch out for the keys used as PRIMARY KEYS.
Then, do this: -
delete from serial where pka_serialNo = 3
-
-
select * from serial
-
select * from parts
-
I change some size on your structure so that it can fit in this window. Also, I simplified the values of Serial and Ticket Nos to single digit. These are just samples.
Hope this helps.
-- CK
Hey CK,
well i ran into the problem with the primary key. It says tbl_CS_serial already has a
primary key defined on it. An you said to watch out for the primary key, does this mean i need to do something different for the primary key part? Here is what i tried -
alter table dbo.tbl_CS_serial
-
add constraint pkserial PRIMARY KEY (pka_serialNo,pkb_fk_ticketNo)
-
-
alter table dbo.tbl_CS_parts
-
add constraint fkserial FOREIGN KEY (fk_serialNo,fk_ticketNo) REFERENCES dbo.tbl_CS_serial (pka_serialNo,pkb_fk_ticketNo) on delete cascade
-
-
-
Thank you,
Rach
The primary key shouldn't be a problem because from what I can see, it's already been set up correctly. It's the parts foreign key you should be looking at.
The primary key shouldn't be a problem because from what I can see, it's already been set up correctly. It's the parts foreign key you should be looking at.
Hey Acoder,
I must admit i am confused on what you mean about the foreign key?
I have checked all the table names and fields an there correct.But i do believe you are right about the foreign key. Because when i tried the "with nocheck" before the add constraint in the serial table again (wanted to see if the primary key was causing the problem like before). But anyway when i tried that it gave me an error again and its the same error saying tbl_CS_serial already has a primary key defined on it..Any Ideas on what i could try? - alter table dbo.tbl_CS_serial
-
add constraint pkserial PRIMARY KEY (pka_serialNo,pkb_fk_ticketNo)
-
-
alter table dbo.tbl_CS_parts
-
add constraint fkserial FOREIGN KEY (fk_serialNo,fk_ticketNo) REFERENCES dbo.tbl_CS_serial (pka_serialNo,pkb_fk_ticketNo) on delete cascade
i was thinking maybe i needed to replace pkserial and fkserial with the column name like pkserial would be pka_serialNo and fkserial would be fk_serialNo. But i am not sure if that would be right.
Thank you,
Rach
Forget the primary key - that's fine. Only try the second alter statement to add the foreign key, but I think that may also have been set up. Once you have that, then test with a delete on serials. If everything's correct, the corresponding parts records should be deleted too.
Forget the primary key - that's fine. Only try the second alter statement to add the foreign key, but I think that may also have been set up. Once you have that, then test with a delete on serials. If everything's correct, the corresponding parts records should be deleted too.
Hey Acoder,
I tried the following - alter table dbo.tbl_CS_parts
-
add constraint fkserial FOREIGN KEY (fk_serialNo,fk_ticketNo) REFERENCES dbo.tbl_CS_serial (pka_serialNo,pkb_fk_ticketNo) on delete cascade
and got the error - Msg 547, Level 16, State 0, Line 2
-
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fkserial". The conflict occurred in database "CustomerSupport", table "dbo.tbl_CS_serial".
-
any ideas?
Thank you,
Rach
It seems you have a data conflict. You have some records in parts that don't match the keys in the serial table. Either use no check or sort out the conflict.
It seems you have a data conflict. You have some records in parts that don't match the keys in the serial table. Either use no check or sort out the conflict.
Hey Acoder,
Well i don't know if this could be causing the conflict. But i do have another column in parts tables (it appears before serial and ticketno) called pk_partID and it holds the maximum number of parts that been added and its a primary key. I don't know if that could cause the conflict? Or could the problem also be that some records that are in parts don't match up with some in serial? because i still have records in there from when i was only adding one part and multiple serials and maybe they could be conflicting? could deleting all the serials and parts and starting fresh with one new record possibly resolve the problem? An if i do no check will that cause problems later between the serial and parts deleting the correct fields?
Thank you,
Rach
Is it all test data in serial and parts? If it is, then yes, deleting the data and starting afresh would resolve the problem.
Is it all test data in serial and parts? If it is, then yes, deleting the data and starting afresh would resolve the problem.
Hey Acoder,
Well that seemed to do the trick no error :). But ok to test the deleting do i need to create a stored procedure or do query analyzer? An then if it is query anaylzer this is all i should need to test correct? - delete from dbo.tbl_CS_serial where pka_serialNo = 3
Thank you,
Rach
Yes, just in the query analyzer. The primary key is composite, so I would check the pkb_ticketNo field as well to be sure: - delete from dbo.tbl_CS_serial where pka_serialNo = 3 and pkb_ticketNo = 2
For this to work, though, you must make sure there's data for that serial no. and ticket no. and in parts too for this serial and ticket number. If not, add some sample data first.
Yes, just in the query analyzer. The primary key is composite, so I would check the pkb_ticketNo field as well to be sure: - delete from dbo.tbl_CS_serial where pka_serialNo = 3 and pkb_ticketNo = 2
For this to work, though, you must make sure there's data for that serial no. and ticket no. and in parts too for this serial and ticket number. If not, add some sample data first.
Hey Acoder,
Well when i tried the following in the query analyzer - delete from dbo.tbl_CS_serial where pka_serialNo = 3 and pkb_fk_ticketNo = 3
i got the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'ser1' to data type int.
weird thing about ser1 is. I created 3 different serials and one of the serials is ser1. Thing is i am not getting rid of serial ser1 trying to get rid of 3 with the ticketNo 3. An with ser1 i didn't created any fields to go into the parts table.
Thank you,
Rach
The fields are nvarchar, so you need to put the 3s in quotes.
The fields are nvarchar, so you need to put the 3s in quotes.
Hey Acoder,
Yay this worked, it delete from both serial and parts :) - delete from dbo.tbl_CS_serial where pka_serialNo = '3' and pkb_fk_ticketNo = '3'
Ok so do i get to create a stored procedure now? an would this be correct or work for the stored procedure -
set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-
-
-
-- =============================================
-
-- Author: <Author,,Name>
-
-- Create date: <Create Date,,>
-
-- Description: <Description,,>
-
-- =============================================
-
CREATE PROCEDURE [dbo].[usp_CS_Deleteserialparts]
-
-- Add the parameters for the stored procedure here
-
(@pka_serialNo nvarchar(100),
-
@pkb_fk_ticketNo nvarchar(100))
-
-
delete from dbo.tbl_CS_serial where (pka_serialNo = @pka_serialNo and pkb_fk_ticketNo = @pkb_fk_ticketNo)
Thank you,
Rach
I should think so. Glad you got it working :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Andrew DeFaria |
last post by:
I created the following .sql file to demonstrate a problem I'm having.
According to the manual:
If |ON DELETE CASCADE| is specified, and a row in the parent table
is deleted, then InnoDB...
|
by: cfxchange |
last post by:
I am looking into work-arounds for what seems to be a flaw, or
"undocumented feature" of SQL Server replication and Instead of Delete
triggers not playing together. It seems that if you want to...
|
by: jim |
last post by:
I have two tables that are related by keys. For instance,
Table employee {
last_name char(40) not null,
first_name char(40) not null,
department_name char(40) not null,
age int not null,
......
|
by: Paul T. Rong |
last post by:
Dear all,
Here is my problem:
There is a table "products" in my access database, since some of the
products are out of date and stopped manufacture, I would like to delete
those PRODUCTS from...
|
by: Mitchell Thomas |
last post by:
I have 3 tables and they are all a one-one relationship. The tables
are in oracle with Access as the front end. If user deletes a record
from Table1, how do I programmatically delete the...
|
by: Tim Marshall |
last post by:
HI all, Access 2003, Jet back end.
Rather than annoy my users in a particular app by having relationships
with enforced relational integrity refuse to delete a record with
related records, I'm...
|
by: John Rivers |
last post by:
Hello,
I think this will apply to alot of web applications:
users want the ability to delete a record in table x
this record is related to records in other tables
and those to others in...
|
by: Karl O. Pinc |
last post by:
Hi,
Thought perhaps some other eyes than mine can tell if I'm doing
something wrong here or if there's a bug somewhere. I've never
passed a ROWTYPE varaible to a function but I don't see where...
|
by: nekiv90 |
last post by:
Greetings,
I have to delete older policies and its related records in other
tables.
The deletion from the parent table will trigger the deletion of
relevant records from about 30 something...
|
by: polocar |
last post by:
Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generations" (in which there is only one table, called...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |