Advise Please

hello
I have two related table

table1
cId
cDesc

table2
Id
Name
phone
cId

These two tables are related
where cId in table 1 is primary key and cId in table2 is foreign key

If I want to delete a record in table 1 which is related to table 2
Which is faster and more accurate

should I write my stored procedure like this
if exists(select * from table2 where cID = @.CID)
delete from table1 where cID = @.CID
else
return 0
-------------
or
-------------
delete from table1 where cID = @.CID

if @.@.error<>0
return 0

Neither of the above will work if you have a primary-foriegn key relationship as it would break the relationship.

Are you wanting to delete the record from table1 and any related records from table2, or do you want to delete from table1 only if there are no references to it in table2?

|||

wel i gues option 2 is faster but first u need to delete it from table 2 otherwise it gives u error

|||

Hi,

I recently ran into such a case and used the following code:
DELETE FROM TABLE2 WHERE id = @.ID
DELETE FROM TABLE1 WHERE id = @.ID
this worked perfectly for me.
But there is other way to achieve your goal:
just define the following foreign key on table2:
FOREIGN KEY (Id) REFERENCES table1 (cID) ON DELETE CASCADE

I hope this heps

|||

The second one

|||

I dont want to delete child if it exists...

Which the better way referenced to my first post

|||

according to my knowledge u need yo delete the refrence from child table i.e. foreign key relation ......

may be im wrong do let me know

|||

It seems that there is a big misunderstanding,

wht I am trying to do is :

check if the parent table has a child, if so message the user u cannot delete (The parent Record)

if the parent does not have a child delete the record..(The parent Record)

So I mentioned in my first post 2 options to do that and I was wondering which is the better and the faster

I hope I made my idea clear now
Thank you

|||

The 2nd option is indeed faster and better depending upon how many rows are in your 2nd table.

|||But is it apropriate to coz sql an error??|||

As long as your trapping the error and resetting the error object I dont see any problems with it. Just be sure you have the FK constraints on your second table otherwise this method will not work.

|||

well now v get the xact n accurate situation dat wht u need n wht u r doing...wel i gues u r on the rite track n again the 2nd option is beter wen eva u get error from sql u can catch it in try catch block n after verifying that its a FK constraint error u can simply display that child record exists.....

|||

Are there any disadvantages using catch block statments??

|||

Are there any disadvantages using "try catch" block statments??

|||

Using Try Catch block statements are highly advisable when you know that there is a possablity of an exception being thrown. Good error handling is always a good idea in any application.