How to delete FK one to many?

Question

Hello,

I have a table (aspnet_Membership) specifically that has spam records in it that I need to delete. There are some foreign keys and I'm trying to write a simple SQL statement to remove the FK's so I can delete the primary record.

So theres a table called 'aspnet_UsersInRoles' that has UserID and RoleID, and in my 'aspnet_Membership' table is the UserID. I can't delete the User without orphaning a record(wouldn't let me do that anyways due to contraints).

How can I essentially run the following:

'delete from 'aspnet_UsersInRoles' where UserID in 'aspnet_Membership' and 'aspnet_Membership.CreateDate >= '03/15/2009'?

Thank you for any suggestions.

Answer 1

You can alter the constraints to do a cascade all upon deletion: link

Otherwise you can look in the sys.foreign_keys table to get all the foreign keys using the primary key and auto generate sql to do deletes there first.

Answer 2

Well, you probably could have set up the foreign keys to do a cascading delete, so that you didn't need to worry about it, but your try at the query was pretty close to one that would work, just use a subquery:

DELETE FROM aspnet_UsersInRoles WHERE UserID IN (SELECT UserID FROM aspnet_Membership WHERE aspnet_Membership.CreateDate >= '03/15/2009')