question

bhanupratapsngh9 avatar image
bhanupratapsngh9 asked

enforce relationship between tables

Dear Geeks !! Good day to you!!! I am struggling with a problem i am given in my organization. There are 3 types of members in a family we have TABLE2 FOR YOU and SPOUSE, TABLE3 for FamilyMembers, TABLE4 for Other Members there is a view which unions TABLE2,TABLE3,TABLE4 and shows columns like csid,Memid,[Full Name] there is TABLE5 which stores data for any cusid,Memid exists in any of (TABLE2,TABLE3,TABLE4) Example: TABLE2 csid,memid,fname/ 100,1,Vijay/ 100,2,Kiran TABLE3 csid,memid,fname/ 100,4,Vijay son name/ 100,5,Vijay Daughter name TABLE4 csid,memid,fname/ 100,6,Vijay Friend name TABLE5 csid,memid,cola,colb,colc/ 100,1,aa,bb,cc/ 100,5,aa,bb,cc/ 100,6,aa,bb,cc/ now what i have to do is if someone tries to delete memid 4 i will permit as table5 have no record for it, but someone tries o delete memid 6, whose record is in table5 i wont allow so what would be best mechanism to implement it. Please assist me as its is urgent. Thanks a ton in advance.
foreign-keyrelationshipsreferential-integrity
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
1 Like 1 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I can't really see that you can do this with a foreign key constraint, since you're saying there can be records in tables 2, 3 and 4 without a corresponding record in table5 and you just want to make sure no record in tables 2, 3 and 4 gets deleted if it does have a corresponding record in table 5. Therefore, I'd probably go for a trigger on tables 2, 3 and 4 which makes a rollback and raises an error if records which are in table5 are deleted. I'm a bit concerned about your database design though. Why do you have three different tables for storing this information? Why not put it in one, with parent/child relations within the table? What if Vijays son has a son of his own? Would Vijays son then get a record in both table3 and table2?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Magnus Ahlkvist, as you responded very soon. actually we take 30 columns data for you and spouse in table 2 but for familymembers we take 5-7 columns data and for others we take only 3-4 data so we did it . and i think if we would have taken it in single table then we have to take near about 20 nulls for each family and other member. and in case of vijayas son vijaya will have to register in our system as new family. now where i am confused is like i have 20 tables like table5 then should i write trigger or there is any other good option.
0 Likes 0 ·
You could also create a stored procedure for the delete, and have that stored procedure look if the record is in use in table5 or not. But if you want to allow for a delete-operation directly against tables 2, 3 and 4, you'd have to create triggers on these tables, checking against table5 if the record exists. Even if it's some 20 tables (or even more), it's still just a one-off to create the triggers. They will look more or less the same, so it shouldn't be more than a few hours work to get it done.
0 Likes 0 ·
i have a stored procedure for the delete, so i will implement if exist there i hope this will help me what i desire. anyway thank you very much.
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
> we take 30 columns data for you and spouse in table 2 but for familymembers we take 5-7 columns data and for others we take only 3-4 data Sounds to me like you need to normalise your structure a little...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.