SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with deleting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iNko
Starting Member

Lithuania
19 Posts

Posted - 03/31/2013 :  12:08:42  Show Profile  Reply with Quote
Here's my database:


I am trying to do something like this:
"DELETE FROM User WHERE user_name_surname ='user_02'"

I dont really know how to explain my problem so ill write a sequence of things that should explain my problem:
- i create a new user - user_01
- i create a new group - group_01
- i assign user_01 to group_01 (User with Membership tables)
- i create a new message with user_01 (Message table)
- i send this message to group_01 (Group_messages table)

Heres where the problem appears:
- i create a new user - user_02
- i assign this user to already existing group - group_01
- i try to delete this user but i cant because group_01 is linked with Group_messages table

So yeah, can i somehow 'unlink' the user from group?

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  00:25:21  Show Profile  Reply with Quote
yep you can

you need to first check for use references in membership and Message tables (Message,Single_messages). First delete them from those tables before you delete the user.

Another way to deal with it is to create foreignkeys with on delete cascade option so that when you delete a user the reference records will also get deleted from other table. But this is NOT RECOMMENDED as without proper documentation this can be a pain to maintain and support.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  00:28:36  Show Profile  Reply with Quote
see below on logic you can use to recursively do deletion from child to parent

http://visakhm.blogspot.in/2011/11/recursive-delete-from-parent-child.html

you just need to add a condition to look only from table and userid you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 04/01/2013 :  08:49:25  Show Profile  Reply with Quote
I think i came up with a simple solution - replacing the relation GROUP_MESSAGES - MEMBERSHIP with GROUP_MESSAGES - GROUP. Now i think everything works how i wanted
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  09:51:07  Show Profile  Reply with Quote
In any case it should be how i suggested. you've to first delete references before you delete actual record from table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000