How to delete data from 4 tables with one MS SQL query

when i delete one data all the data related to that should also be deleted

[nisha albert]

i have a problem in the MS SQL query..

i am using windows 2000 professional and MS SQL 2000

i have 4 tables

tables & fields:
1. login_det (user_id, pwd)
2. user_det (user_id, user_name, email_id
3. post_query (user_id, query_id, contents)
4. reply_query (user_id, query_id, contents, reply)

in login_det, i have a field named user_id. it is a primary key in that table.

all the other 3 tables contain user_id and it is the foriegn key in all that tables.

when i delete details related to a particular user_id from one table, i want all the details related to that particular user_id to be deleted from all the tables.

i tried it in many ways using joins and all.. but i got an error message saying that it cannot be done because of primary and forign key relations.

can anybody solve my problem... plzzzzzzzzzzz.....

thanks in advance....

[aspdaddy]

Easy - you need to tell your foreign key to cascade deletes:

ALTER TABLE [myTable1] ADD 
CONSTRAINT [myForeignKey] FOREIGN KEY 
(
[myID]
) REFERENCES [myTable2] (
[myTable1ID]
) ON DELETE CASCADE 
)

Map out all you cascade rules first, make sure there are no circular references etc.