Table Relationshp Question

[Robert]

I have a simple database with 3 tables, Companies, Contacts and
Indusrty.

Companies
CompanyID (Primary Key) CompanyName Industry

Contacts
ContactID (Primary Key) FirstName CompanyiD

Industry
Industry (Primary ID)

There is a One to Many relationship between

Companies.CompanyID and Contacts.CompanyID

Industry.Industry and Companies.Industry.

I have enabled cascasding deletions for both relationships.

When I delete a company all related contacts are deleted, as expected.
However it does not delete the industry from the Industry table
(assuming the industry is only assigned to that one company I am
deleting. Am I mis-understanding how this works? It works they way I
expect it to between companies and contacts but not Companies and
Industry table. Am I missing something here?

Thanks.

[PC Datasheet]

Cascade Delete only works when you try to delete a record on the 1 side of a 1-M
relationship not when you try to delete a record on the M side. Industry in
Companies is on the M side so you get no cascade delete. If you would try to
delete Industry in the Industry table, cascade would work and delete all company
records that contained that Industry value. BTW, the cascade would carry forward
and delete the Contacts associated with the companies that were being deleted.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Robert" <Ro************@PJSolomon.Com> wrote in message
news:60*************************@posting.google.co m...

I have a simple database with 3 tables, Companies, Contacts and
Indusrty.

Companies
CompanyID (Primary Key) CompanyName Industry

Contacts
ContactID (Primary Key) FirstName CompanyiD

Industry
Industry (Primary ID)

There is a One to Many relationship between

Companies.CompanyID and Contacts.CompanyID

Industry.Industry and Companies.Industry.

I have enabled cascasding deletions for both relationships.

When I delete a company all related contacts are deleted, as expected.
However it does not delete the industry from the Industry table
(assuming the industry is only assigned to that one company I am
deleting. Am I mis-understanding how this works? It works they way I
expect it to between companies and contacts but not Companies and
Industry table. Am I missing something here?

Thanks.

[Bradley]

Is the relationship between Industry & Company the correct way around?

The deletion will cascade "down" the relationships, not "up". If Industry is
a parent of Company... if you delete an Industry record it would delete all
associated Companies and Contacts.

What if the Industry is used by a different Company record? That is why it
won't be removed.
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response
"Robert" <Ro************@PJSolomon.Com> wrote in message
news:60*************************@posting.google.co m...

I have a simple database with 3 tables, Companies, Contacts and
Indusrty.

Companies
CompanyID (Primary Key) CompanyName Industry

Contacts
ContactID (Primary Key) FirstName CompanyiD

Industry
Industry (Primary ID)

There is a One to Many relationship between

Companies.CompanyID and Contacts.CompanyID

Industry.Industry and Companies.Industry.

I have enabled cascasding deletions for both relationships.

When I delete a company all related contacts are deleted, as expected.
However it does not delete the industry from the Industry table
(assuming the industry is only assigned to that one company I am
deleting. Am I mis-understanding how this works? It works they way I
expect it to between companies and contacts but not Companies and
Industry table. Am I missing something here?

Thanks.