delete cascade for 2 tables?

[bonneylake]

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

[ck9663]

Would you mind posting the actual structure (like a create table) and some sample data? We'll try and built it for you.

-- CK

[bonneylake]

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

[ck9663]

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

[bonneylake]

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

[ck9663]

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

[bonneylake]

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

[acoder]

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.

[bonneylake]

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

[acoder]

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.

[bonneylake]

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

[acoder]

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.

[bonneylake]

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

[acoder]

Is it all test data in serial and parts? If it is, then yes, deleting the data and starting afresh would resolve the problem.

[bonneylake]

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