By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,453 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

SQL Delete

P: 92
Hi

Anyone suggest me a query for, if i delete master table values it must delete all its dependent table.

ie.
I hv 2 tables. Fields as

Master table.
1. MID
2. Name

Child Table
1.MID
2.Child Name.

if i delete Master table record. it must automatically delete its child table record.
how can i do this using SQL query.

Suggest me plz.

Thanks in Advance.
May 12 '08 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
1) you need to set the primary key on the maser table on field MID
2) I think you will need to add a field to the child table called ChildID
and then set the primary key to both MID and ChildID on that table
3) Create a relationships diagram with those two table in it
4) Connect the two tables on the MID field and turn on "Cascade delete related records"
May 12 '08 #2

P: 92
1) you need to set the primary key on the maser table on field MID
2) I think you will need to add a field to the child table called ChildID
and then set the primary key to both MID and ChildID on that table
3) Create a relationships diagram with those two table in it
4) Connect the two tables on the MID field and turn on "Cascade delete related records"


Thanks 4 replying me

why i should create ChildID. with that MID itself i wnt to delete it. Can u suggest me the query for this delete processing.

Explain me clearly with query plz
May 12 '08 #3

Delerna
Expert 100+
P: 1,134
OK i just did a test and you don't actually need to add the field or set the primary key on the child table

So this is now what you need to do
1) you need to set the primary key on the maser table on field MID
2) Create a relationships diagram with those two table in it
3) Connect the two tables on the MID field and turn on "Cascade delete related records"

The delete query will be just a standard delete query. The query is not important to the discussion here.

The thing that makes it work is the "cascade delete related fields" option in the relationships diagram. When you delete a record from the master table any related records in the child table will automatically be deleted
May 12 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.