Problem with deleting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with deleting

Author

Topic

iNko
Starting Member

19 Posts

Posted - 2013-03-31 : 12:08:42

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

52326 Posts

Posted - 2013-04-01 : 00:25:21

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 00:28:36

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

iNko
Starting Member

19 Posts

Posted - 2013-04-01 : 08:49:25

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 09:51:07

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources