SQL Delete

[Limno]

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.

[Delerna]

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"

[Limno]

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

[Delerna]

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