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

Table Relationshp Question

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.