By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

delete cascade for 2 tables?

100+
P: 769
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.


Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2.  set QUOTED_IDENTIFIER ON
  3.  go
  4.  
  5.  
  6.  -- =============================================
  7. . -- Author:        <Author,,Name>
  8.  -- Create date: <Create Date,,>
  9.  -- Description:    <Description,,>
  10. -- =============================================
  11. CREATE PROCEDURE [dbo].[usp_CS_Deletesp]
  12.  -- Add the parameters for the stored procedure here
  13. (
  14.  @pkb_fk_ticketNo nvarchar(100),
  15. @fk_ticketNo nvarchar(100)) AS
  16.  
  17. DELETE FROM tbl_CS_serial
  18. WHERE pkb_fk_ticketNo=@pkb_fk_ticketNo
  19.  
  20. DELETE FROM tbl_CS_parts
  21.  WHERE fk_ticketNo=@fk_ticketNo
Thank you in advance,
Rach
Oct 29 '08 #1
Share this Question
Share on Google+
50 Replies


acoder
Expert Mod 15k+
P: 16,027
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?
Oct 29 '08 #2

100+
P: 769
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
Oct 29 '08 #3

acoder
Expert Mod 15k+
P: 16,027
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?
Oct 29 '08 #4

100+
P: 769
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
Expand|Select|Wrap|Line Numbers
  1. pk_partId (primary key)
  2. fk_serialNo
  3. fk_ticketNo
  4. hc_partNo
  5. part_returned
  6. rma_number
  7. defective
  8. submission
serial table
Expand|Select|Wrap|Line Numbers
  1. pka_serialNo(primary key)
  2. pkb_fk_ticketNo(primary key)
  3. model_no
  4. product_type
  5. software_hardware
  6. resolution
  7. resolution_date
  8. verification_date
  9. rma_data
  10. type_hardware_failure
  11. dept_responsibility
  12. resoulution_verified_by
Thank you,
Rach
Oct 29 '08 #5

acoder
Expert Mod 15k+
P: 16,027
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.
Oct 29 '08 #6

100+
P: 769
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
Oct 29 '08 #7

acoder
Expert Mod 15k+
P: 16,027
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.
Oct 29 '08 #8

ck9663
Expert 2.5K+
P: 2,878
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
Oct 29 '08 #9

100+
P: 769
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
Oct 29 '08 #10

ck9663
Expert 2.5K+
P: 2,878
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
Oct 29 '08 #11

100+
P: 769
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
Oct 29 '08 #12

ck9663
Expert 2.5K+
P: 2,878
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
Oct 29 '08 #13

100+
P: 769
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.

Expand|Select|Wrap|Line Numbers
  1. CREATE Procedure spDeleteRows
  2. /* 
  3. Recursive row delete procedure. 
  4.  
  5. It deletes all rows in the table specified that conform to the criteria selected, 
  6. while also deleting any child/grandchild records and so on.  This is designed to do the 
  7. same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
  8. table to find any child tables, then deletes the soon-to-be orphan records from them using 
  9. recursive calls to this procedure. Once all child records are gone, the rows are deleted 
  10. from the selected table.   It is designed at this time to be run at the command line. It could 
  11. also be used in code, but the printed output will not be available.
  12. */
  13.     (
  14.     @dbo.usp_CS_serial, /* name of the table where rows are to be deleted */
  15.     @pka_fk_ticketNo nvarchar(100), /* criteria used to delete the rows required */
  16.     @iRowsAffected int OUTPUT /* number of records affected by the delete */
  17.     )
  18. As
  19. set nocount on
  20. declare     @dbo.tbl_CS_parts, /* name of the child table */
  21.     @fk_ticketNo nvarchar(100), /* name of the linking field on the child table */
  22.     @dbo.usp_CS_serial, /* name of the parent table */
  23.     @pkb_fk_ticketNo nvarchar(100), /* name of the linking field in the parent table */
  24.     @fk_serialNo nvarchar(100), /* name of the foreign key */
  25.     @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
  26.     @fk_ticketNo nvarchar(100), /* criteria to be used to delete 
  27.                                            records from the child table */
  28.     @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


Expand|Select|Wrap|Line Numbers
  1. /* declare the cursor containing the foreign key constraint information */
  2. DECLARE cFKey CURSOR LOCAL FOR 
  3. SELECT SO1.name AS Tab, 
  4.        SC1.name AS Col, 
  5.        SO2.name AS RefTab, 
  6.        SC2.name AS RefCol, 
  7.        FO.name AS FKName
  8. FROM dbo.sysforeignkeys FK  
  9. INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
  10.                               AND FK.fkey = SC1.colid 
  11. INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
  12.                               AND FK.rkey = SC2.colid 
  13. INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
  14. INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
  15. INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
  16. WHERE SO2.Name = @cTableName
  17.  
  18. OPEN cFKey
  19. FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
  20. WHILE @@FETCH_STATUS = 0
  21.      BEGIN
  22.     /* build the criteria to delete rows from the child table. As it uses the 
  23.            criteria passed to this procedure, it gets progressively larger with 
  24.            recursive calls */
  25.     SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 
  26.                               @cRefTab +'] WHERE ' + @cCriteria + ')'
  27.     print 'Deleting records from table ' + @cTab
  28.     /* call this procedure to delete the child rows */
  29.     EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
  30.     FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
  31.      END
  32. Close cFKey
  33. DeAllocate cFKey
  34. /* finally delete the rows from this table and display the rows affected  */
  35. SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
  36. print @cSQL
  37. EXEC sp_ExecuteSQL @cSQL
  38. print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName
Thank you,
Rach
Oct 29 '08 #14

100+
P: 769
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.

Expand|Select|Wrap|Line Numbers
  1. <!---/* declare the cursor containing the foreign key constraint information */
  2. DECLARE cFKey CURSOR LOCAL FOR 
  3. SELECT SO1.name AS Tab, 
  4.        SC1.name AS Col, 
  5.        SO2.name AS RefTab, 
  6.        SC2.name AS RefCol, 
  7.        FO.name AS FKName
  8. FROM dbo.sysforeignkeys FK  
  9. INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
  10.                               AND FK.fkey = SC1.colid 
  11. INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
  12.                               AND FK.rkey = SC2.colid 
  13. INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
  14. INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
  15. INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
  16. WHERE SO2.Name = @cTableName
  17.  
  18. OPEN cFKey
  19. FETCH NEXT FROM cFKey INTO @dbo.tbl_CS_parts, @fk_ticketNo, @dbo.usp_CS_serial, @pkb_fk_ticketNo, @fk_serialNo
  20. WHILE @@FETCH_STATUS = 0--->
but here is what i have right now in full

Expand|Select|Wrap|Line Numbers
  1. CREATE Procedure spDeleteRows
  2. /* 
  3. Recursive row delete procedure. 
  4.  
  5. It deletes all rows in the table specified that conform to the criteria selected, 
  6. while also deleting any child/grandchild records and so on.  This is designed to do the 
  7. same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
  8. table to find any child tables, then deletes the soon-to-be orphan records from them using 
  9. recursive calls to this procedure. Once all child records are gone, the rows are deleted 
  10. from the selected table.   It is designed at this time to be run at the command line. It could 
  11. also be used in code, but the printed output will not be available.
  12. */
  13.     (
  14.     @dbo.usp_CS_serial, /* name of the table where rows are to be deleted */
  15.     @pka_fk_ticketNo nvarchar(100), /* criteria used to delete the rows required */
  16.     @iRowsAffected int OUTPUT /* number of records affected by the delete */
  17.     )
  18. As
  19. set nocount on
  20. declare     @dbo.tbl_CS_parts, /* name of the child table */
  21.     @fk_ticketNo nvarchar(100), /* name of the linking field on the child table */
  22.     @dbo.usp_CS_serial, /* name of the parent table */
  23.     @pkb_fk_ticketNo nvarchar(100), /* name of the linking field in the parent table */
  24.     @fk_serialNo nvarchar(100), /* name of the foreign key */
  25.     @cSQL ,  query string passed to the sp_ExecuteSQL procedure 
  26.     @fk_ticketNo nvarchar(100), /* criteria to be used to delete 
  27.                                            records from the child table */
  28.     @iChildRows int /* number of rows deleted from the child table */
  29.  
  30. <!---/* declare the cursor containing the foreign key constraint information */
  31. DECLARE cFKey CURSOR LOCAL FOR 
  32. SELECT SO1.name AS Tab, 
  33.        SC1.name AS Col, 
  34.        SO2.name AS RefTab, 
  35.        SC2.name AS RefCol, 
  36.        FO.name AS FKName
  37. FROM dbo.sysforeignkeys FK  
  38. INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
  39.                               AND FK.fkey = SC1.colid 
  40. INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
  41.                               AND FK.rkey = SC2.colid 
  42. INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
  43. INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
  44. INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
  45. WHERE SO2.Name = @cTableName
  46.  
  47. OPEN cFKey
  48. FETCH NEXT FROM cFKey INTO @dbo.tbl_CS_parts, @fk_ticketNo, @dbo.usp_CS_serial, @pkb_fk_ticketNo, @fk_serialNo
  49. WHILE @@FETCH_STATUS = 0--->
  50.      BEGIN
  51.     /* build the criteria to delete rows from the child table. As it uses the 
  52.            criteria passed to this procedure, it gets progressively larger with 
  53.            recursive calls */
  54.     SET @fk_ticketNo = @fk_ticketNo + ' in (SELECT [' + @pkb_fk_ticketNo + '] FROM [' + 
  55.                               @dbo.usp_CS_serial +'] WHERE ' + @pka_fk_ticketNo + ')'
  56.     <!---print 'Deleting records from table ' + @cTab--->
  57.     /* call this procedure to delete the child rows */
  58.     EXEC spDeleteRows @dbo.tbl_CS_parts, @fk_ticketNo, @iChildRows OUTPUT
  59.     FETCH NEXT FROM cFKey INTO @dbo.tbl_CS_parts, @fk_ticketNo, @dbo.usp_CS_serial, @pkb_fk_ticketNo, @fk_serialNo
  60.      END
  61. <!---Close cFKey
  62. DeAllocate cFKey--->
  63. /* finally delete the rows from this table and display the rows affected  */
  64. SET @cSQL = 'DELETE FROM [' + @dbo.usp_CS_serial + '] WHERE ' + @pka_fk_ticketNo
  65. <!---print @cSQL--->
  66. EXEC sp_ExecuteSQL @cSQL
  67. <!---print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @dbo.usp_CS_serial--->
Thank you,
Rach
Oct 29 '08 #15

acoder
Expert Mod 15k+
P: 16,027
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.
Oct 30 '08 #16

ck9663
Expert 2.5K+
P: 2,878
Sorry, I gave you the wrong link. I'm really sorry.

Here's a sample of CASCADE

Consider this sample table:

Expand|Select|Wrap|Line Numbers
  1. set nocount on
  2.  
  3. create table Authors
  4. (AuthorID int not null, AuthorName varchar(30), AuthorCity varchar(15))
  5.  
  6. create table Books
  7. (aid int not null, BookID int not null, BookTitle varchar(30), BookPrice money)
  8.  
  9. insert into Authors values (1, 'Jhon', 'San Francisco')
  10. insert into Authors values (2, 'Paul', 'Los Angeles')
  11. insert into Authors values (3, 'George', 'San Diego')
  12. insert into Authors values (4, 'Ringo', 'Oakland')
  13.  
  14. insert into Books values (1, 1, 'Yesterday',5.00)
  15. insert into Books values (1, 2, 'In My Life',7.50)
  16. insert into Books values (1, 3, 'Hey Junde',4.45)
  17. insert into Books values (2, 4, 'Fool On the Hill',NULL)
  18. insert into Books values (2, 6, 'If I Fell',7.80)
  19. insert into Books values (4, 7, 'Let It Be',NULL)
  20. insert into Books values (4, 8, 'Till There Was You',0.00)
  21. insert into Books values (2, 9, 'Yellow Submarine',34.65)
  22. insert into Books values
  23.  (1, 10, 'I Should Have Known Better',65.33)
  24.  
  25. select * from authors
  26. select * from books
  27.  
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.

Expand|Select|Wrap|Line Numbers
  1. alter table authors add primary key (authorid)
  2.  
  3. alter table books add primary key (aid, bookid)
  4. alter table books add constraint fkaid foreign key (aid) references authors(authorid) on delete cascade

Now, issue a delete on the parent table:

Expand|Select|Wrap|Line Numbers
  1.  
  2. delete from Authors where AuthorID = 2
  3.  
  4. select * from Authors
  5. select * from Books
  6.  
  7.  

Paste the entire code in SQL Analyzer and try it.

-- CK
Oct 30 '08 #17

100+
P: 769
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.

Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5.  
  6.  
  7. -- =============================================
  8. -- Author:        <Author,,Name>
  9. -- Create date: <Create Date,,>
  10. -- Description:    <Description,,>
  11. -- =============================================
  12. ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
  13.     -- Add the parameters for the stored procedure here
  14.  
  15. ALTER TABLE tbl_CS_serial add primary key(pka_serialNo,pkb_fk_ticketNo)
  16. ALTER TABLE tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
  17. ALTER TABLE tbl_CS_parts add constraint 
  18. fkfk_serialNo foreign key(fk_serialNo)
  19. references tbl_CS_serial(pkb_fk_ticketNo) on delete cascade
  20.  
  21. delete from tbl_CS_serial where (pkb_fk_ticketNo = @pkb_fk_ticketNo and fk_ticketNo = @fk_ticketNo)
  22.  
  23. select * from tbl_CS_serial
  24. select * from tbl_CS_parts
  25.  

here is what i currently have

Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5.  
  6.  
  7. -- =============================================
  8. -- Author:        <Author,,Name>
  9. -- Create date: <Create Date,,>
  10. -- Description:    <Description,,>
  11. -- =============================================
  12. ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
  13.     -- Add the parameters for the stored procedure here
  14.  
  15. ALTER TABLE tbl_CS_serial add primary key(pkb_fk_ticketNo)
  16. ALTER TABLE tbl_CS_parts add primary key (fk_ticketNo)
  17. ALTER TABLE tbl_CS_parts add constraint 
  18. fkaid foreign key()
  19. references tbl_CS_serial(pkb_fk_ticketNo) on delete cascade
  20.  
  21. delete from tbl_CS_serial where pkb_fk_ticketNo = @pkb_fk_ticketNo
  22.  
  23. select * from tbl_CS_serial
  24. select * from tbl_CS_parts
Thank you,
Rach
Oct 30 '08 #18

100+
P: 769
Hey CK,

This is actually what i currently have, made changes since last post.

Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3.  go
  4.  
  5.  -- =============================================
  6.  -- Author:        <Author,,Name>
  7.  -- Create date: <Create Date,,>
  8.  -- Description:    <Description,,>
  9.  -- =============================================
  10. CREATE PROCEDURE [dbo].[usp_CS_Deleteserialparts]
  11.    -- Add the parameters for the stored procedure here
  12.     (
  13.      @pkb_fk_ticketNo nvarchar(100),
  14.      @fk_ticketNo nvarchar(100)) AS
  15.  
  16. ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
  17. ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
  18. ALTER TABLE dbo.tbl_CS_parts add constraint 
  19. fkfk_serialNo foreign key(fk_serialNo)
  20. references dbo.tbl_CS_serial(pkb_fk_ticketNo) on delete cascade
  21.  
  22. delete from dbo.tbl_CS_serial where pkb_fk_ticketNo = @pkb_fk_ticketNo
  23.  
  24. select * from dbo.tbl_CS_serial
  25. select * from dbo.tbl_CS_parts
Thank you,
Rach
Oct 30 '08 #19

acoder
Expert Mod 15k+
P: 16,027
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.
Oct 30 '08 #20

100+
P: 769
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?


Expand|Select|Wrap|Line Numbers
  1.  set ANSI_NULLS ON
  2.  set QUOTED_IDENTIFIER ON
  3.   go
  4.  
  5.  -- =============================================
  6.   -- Author:        <Author,,Name>
  7.   -- Create date: <Create Date,,>
  8.  -- Description:    <Description,,>
  9.  -- =============================================
  10.  CREATE PROCEDURE [dbo].[usp_CS_Deleteserialparts]
  11.     -- Add the parameters for the stored procedure here
  12.      (
  13.       @pkb_fk_ticketNo nvarchar(100),
  14.      @fk_ticketNo nvarchar(100)) AS
  15.  
  16. ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo,pka_serialNo)
  17. ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
  18. ALTER TABLE dbo.tbl_CS_parts add constraint 
  19. fkfk_serialNo foreign key(fk_serialNo,fk_ticketNo)
  20. references dbo.tbl_CS_serial(pkb_fk_ticketNo,pka_serialNo) on delete cascade
  21.  
  22. delete from dbo.tbl_CS_serial where (pkb_fk_ticketNo = @pkb_fk_ticketNo
  23. and fk_ticketNo = @fk_ticketNo)
  24.  
  25. select * from dbo.tbl_CS_serial
  26. select * from dbo.tbl_CS_parts


Thank you,
Rach
Oct 30 '08 #21

acoder
Expert Mod 15k+
P: 16,027
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:
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE dbo.tbl_CS_parts add constraint 
  2. fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
  3. 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.
Oct 30 '08 #22

100+
P: 769
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:
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE dbo.tbl_CS_parts add constraint 
  2. fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
  3. 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.

Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5.  
  6.  -- =============================================
  7.  -- Author:        <Author,,Name>
  8.  -- Create date: <Create Date,,>
  9.  -- Description:    <Description,,>
  10.  -- =============================================
  11. ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
  12.    -- Add the parameters for the stored procedure here
  13.     (
  14.      @pkb_fk_ticketNo nvarchar(100),
  15.      @pka_serialNo nvarchar(100)) AS
  16.  
  17. ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
  18. ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
  19. ALTER TABLE dbo.tbl_CS_parts add constraint 
  20. fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
  21. references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
  22.  
  23. delete from dbo.tbl_CS_serial where (pkb_fk_ticketNo = @pkb_fk_ticketNo
  24.  and pka_serialNo = @pka_serialNo)
  25.  
  26. select * from dbo.tbl_CS_serial
  27. select * from dbo.tbl_CS_parts
Thank you,
Rach
Oct 30 '08 #23

acoder
Expert Mod 15k+
P: 16,027
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.
Oct 30 '08 #24

100+
P: 769
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

Expand|Select|Wrap|Line Numbers
  1. where (pkb_fk_ticketNo = @pkb_fk_ticketNo
  2.  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?

Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5.  
  6.  -- =============================================
  7.  -- Author:        <Author,,Name>
  8.  -- Create date: <Create Date,,>
  9.  -- Description:    <Description,,>
  10.  -- =============================================
  11. ALTER PROCEDURE [dbo].[usp_CS_Deleteserialparts]
  12.    -- Add the parameters for the stored procedure here
  13.  
  14.  
  15. ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
  16. ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
  17. ALTER TABLE dbo.tbl_CS_parts add constraint 
  18. fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
  19. references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
  20.  
  21. delete from dbo.tbl_CS_serial 


Thank you,
Rach
Oct 30 '08 #25

acoder
Expert Mod 15k+
P: 16,027
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.
Oct 30 '08 #26

100+
P: 769
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.

Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
  2. ALTER TABLE dbo.tbl_CS_parts add primary key (fk_serialNo, fk_ticketNo)
  3. ALTER TABLE dbo.tbl_CS_parts add constraint 
  4. fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
  5. references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
  6.  
  7. delete from dbo.tbl_CS_serial 
Thank you,
Rach
Oct 30 '08 #27

acoder
Expert Mod 15k+
P: 16,027
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?
Oct 30 '08 #28

100+
P: 769
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?

Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE dbo.tbl_CS_serial add primary key (pkb_fk_ticketNo)
  2. 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)

Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE dbo.tbl_CS_parts add constraint 
  2. fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
  3. references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
  4.  
  5. delete from dbo.tbl_CS_serial 
Thank you,
Rach
Oct 30 '08 #29

acoder
Expert Mod 15k+
P: 16,027
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?
Oct 30 '08 #30

100+
P: 769
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.

Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE dbo.tbl_CS_parts with nocheck add constraint 
  2. fkfk_serialNo foreign key(fk_ticketNo, fk_serialNo)
  3. 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.

Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE dbo.tbl_CS_parts  add constraint 
  2. fk_serialNo foreign key(fk_ticketNo, fk_serialNo)
  3. references dbo.tbl_CS_serial(pka_serialNo, pkb_fk_ticketNo) on delete cascade
  4.  

Thank you,
Rach
Oct 30 '08 #31

ck9663
Expert 2.5K+
P: 2,878
Would you mind posting the actual structure (like a create table) and some sample data? We'll try and built it for you.

-- CK
Oct 30 '08 #32

100+
P: 769
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

Expand|Select|Wrap|Line Numbers
  1. @pka_serialNo nvarchar(100),
  2.        @pkb_fk_ticketNo nvarchar(100),
  3.        @model_no nvarchar(50),
  4.        @product_type nvarchar(100),
  5.        @software_hardware nvarchar(40),
  6.        @resolution nvarchar(500),
  7.        @resolution_date datetime,
  8.        @resolution_verified_by nvarchar(50),
  9.        @verification_date datetime,
  10.        @dept_responsibility nvarchar(50),
  11.        @type_hardware_failure nvarchar(100)
In the parts table, these are all the fields

Expand|Select|Wrap|Line Numbers
  1. @fk_serialNo nvarchar(100),
  2.     @fk_ticketNo nvarchar(100),
  3.     @hc_partNo nvarchar(50),
  4.     @part_returned char(10),
  5.     @defective bit,
  6.     @rma_number nvarchar(50)
here is the information i put for each field in serial

Expand|Select|Wrap|Line Numbers
  1. pka_serialNo = 323
  2. pkb_fk_ticketNo =  444
  3. model_no = 340LPB 
  4. product_type = Desktop
  5. software_hardware = Nic
  6. resolution =  testres
  7. resolution_date = 10/30/2008
  8. resolution_verified_by = Darrell McCullum
  9. verification_date = 10/30/2008
  10. dept_responsibility = Unknown at this time
  11. type_hardware_failure = DOA
here is the information i put for each field in parts

Expand|Select|Wrap|Line Numbers
  1. fk_serialNo = 323
  2.  fk_ticketNo =  444
  3. hc_partNo = part2
  4. part_returned = 1
  5. defective = 1
  6. rma_number = dasfda
  7.  
Thank you,
Rach
Oct 30 '08 #33

ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. select * from Authors
  2.  
  3. select * from Books
  4.  
  5. AuthorID    AuthorName                     AuthorCity
  6. ----------- ------------------------------ ---------------
  7. 1           Jhon                           San Francisco
  8. 3           George                         San Diego
  9. 4           Ringo                          Oakland
  10.  
  11. aid         BookID      BookTitle                      BookPrice
  12. ----------- ----------- ------------------------------ ---------------------
  13. 1           1           Yesterday                      5.00
  14. 1           2           In My Life                     7.50
  15. 1           3           Hey Junde                      4.45
  16. 1           10          I Should Have Known Better     65.33
  17. 4           7           Let It Be                      NULL
  18. 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
Oct 31 '08 #34

100+
P: 769
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.

Expand|Select|Wrap|Line Numbers
  1.  select * from dbo.tbl_CS_serial
  2.  
  3.  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

Expand|Select|Wrap|Line Numbers
  1. pka_serialNo (primary key)
  2. ------------------ 
  3.  323                       
  4.  
  5.  
  6. pkb_fk_ticketNo (primary key)
  7. -----------------
  8. 444
  9.  
  10.  
  11. model_no
  12. ----------------------
  13. 340LPB
  14.  
  15.  
  16. product_type
  17. ---------------------
  18. Desktop
  19.  
  20.  
  21. software_hardware
  22. -----------------------
  23. Nic
  24.  
  25.  
  26. resolution  
  27. --------------  
  28. testres              
  29.  
  30.  
  31. resolution_date
  32. ------------------
  33. 10/30/2008
  34.  
  35.  
  36. verification_date
  37. ----------------------
  38. 10/30/2008
  39.  
  40.  
  41. type_hardware_failure
  42. -----------------------
  43. DOA
  44.  
  45.  
  46. dept_responsibility      
  47. ------------------------------   
  48. Unknown at this time   
  49.  
  50.  
  51. resolution_verified_by
  52. ------------------------------
  53. Darrell McCullum
  54.  
an this is the parts table

Expand|Select|Wrap|Line Numbers
  1.  
  2. fk_serialNo    
  3.  --------------   
  4.   323           
  5.  
  6.  
  7. fk_ticketNo
  8. ----------------
  9. 444
  10.  
  11.  
  12. hc_partNo
  13. --------------
  14. part2
  15.  
  16.  
  17. part_returned
  18. ------------------
  19. 1
  20.  
  21.  
  22. rma_number
  23. ------------------
  24.  dasfda 
  25.  
  26.  
  27. defective
  28. -------------
  29. true
  30.  
  31.  
  32.  
  33.  
Thank you,
Rach
Oct 31 '08 #35

ck9663
Expert 2.5K+
P: 2,878
Here's a sample data of your table :

Expand|Select|Wrap|Line Numbers
  1. select * from serial
  2. select * from parts
  3.  
  4. pka_serialNo pkb_fk_ticketNo model_no   product_type         software_hardware    resolution           resolution_date         resolution_verified_by verification_date       dept_responsibility  type_hardware_failure
  5. ------------ --------------- ---------- -------------------- -------------------- -------------------- ----------------------- ---------------------- ----------------------- -------------------- ---------------------
  6. 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
  7. 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
  8. 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
  9. 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
  10.  
  11. fk_serialNo fk_ticketNo hc_partNo            part_returned defective rma_number
  12. ----------- ----------- -------------------- ------------- --------- --------------------
  13. 3           3           part2                1             1         dasfda
  14. 1           1           part2                1             1         dasfda
  15. 2           2           part2                1             1         dasfda
  16. 4           4           part2                1             1         dasfda
  17.  
  18.  
Now, let's create the PRIMARY and FOREIGN KEYS.

Expand|Select|Wrap|Line Numbers
  1. alter table serial 
  2. add constraint pkserial PRIMARY KEY (pka_serialNo,pkb_fk_ticketNo)
  3.  
  4. alter table parts 
  5. add constraint fkserial FOREIGN KEY (fk_serialNo,fk_ticketNo) REFERENCES serial (pka_serialNo,pkb_fk_ticketNo) on delete cascade
  6.  
If they're already existing, it will show an error. However, watch out for the keys used as PRIMARY KEYS.

Then, do this:
Expand|Select|Wrap|Line Numbers
  1. delete from serial where pka_serialNo = 3
  2.  
  3. select * from serial
  4. select * from parts
  5.  
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
Nov 2 '08 #36

100+
P: 769
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


Expand|Select|Wrap|Line Numbers
  1. alter table dbo.tbl_CS_serial
  2. add constraint pkserial PRIMARY KEY (pka_serialNo,pkb_fk_ticketNo)
  3.  
  4. alter table dbo.tbl_CS_parts 
  5. add constraint fkserial FOREIGN KEY (fk_serialNo,fk_ticketNo) REFERENCES dbo.tbl_CS_serial (pka_serialNo,pkb_fk_ticketNo) on delete cascade
  6.  
  7.  
  8.  
Thank you,
Rach
Nov 3 '08 #37

acoder
Expert Mod 15k+
P: 16,027
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.
Nov 3 '08 #38

100+
P: 769
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?

Expand|Select|Wrap|Line Numbers
  1. alter table dbo.tbl_CS_serial
  2. add constraint pkserial PRIMARY KEY (pka_serialNo,pkb_fk_ticketNo)
  3.  
  4. alter table dbo.tbl_CS_parts 
  5. 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
Nov 3 '08 #39

acoder
Expert Mod 15k+
P: 16,027
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.
Nov 3 '08 #40

100+
P: 769
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

Expand|Select|Wrap|Line Numbers
  1. alter table dbo.tbl_CS_parts 
  2. 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

Expand|Select|Wrap|Line Numbers
  1. Msg 547, Level 16, State 0, Line 2
  2. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fkserial". The conflict occurred in database "CustomerSupport", table "dbo.tbl_CS_serial".
  3.  
any ideas?

Thank you,
Rach
Nov 3 '08 #41

acoder
Expert Mod 15k+
P: 16,027
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.
Nov 3 '08 #42

100+
P: 769
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
Nov 3 '08 #43

acoder
Expert Mod 15k+
P: 16,027
Is it all test data in serial and parts? If it is, then yes, deleting the data and starting afresh would resolve the problem.
Nov 3 '08 #44

100+
P: 769
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?

Expand|Select|Wrap|Line Numbers
  1. delete from dbo.tbl_CS_serial where pka_serialNo = 3
Thank you,
Rach
Nov 3 '08 #45

acoder
Expert Mod 15k+
P: 16,027
Yes, just in the query analyzer. The primary key is composite, so I would check the pkb_ticketNo field as well to be sure:
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 3 '08 #46

100+
P: 769
Yes, just in the query analyzer. The primary key is composite, so I would check the pkb_ticketNo field as well to be sure:
Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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
Nov 3 '08 #47

acoder
Expert Mod 15k+
P: 16,027
The fields are nvarchar, so you need to put the 3s in quotes.
Nov 3 '08 #48

100+
P: 769
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 :)

Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5.  
  6.  
  7. -- =============================================
  8. -- Author:        <Author,,Name>
  9. -- Create date: <Create Date,,>
  10. -- Description:    <Description,,>
  11. -- =============================================
  12. CREATE PROCEDURE [dbo].[usp_CS_Deleteserialparts]
  13.     -- Add the parameters for the stored procedure here
  14.       (@pka_serialNo nvarchar(100),
  15.       @pkb_fk_ticketNo nvarchar(100))
  16.  
  17. delete from dbo.tbl_CS_serial where (pka_serialNo = @pka_serialNo and pkb_fk_ticketNo = @pkb_fk_ticketNo)
Thank you,
Rach
Nov 3 '08 #49

acoder
Expert Mod 15k+
P: 16,027
I should think so. Glad you got it working :)
Nov 3 '08 #50

50 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.