Referential Integrity

[Brant]

I have 3 tables, Company, Audit, and Network (I have more than this but
this is all i need for my question) Company And Audit are related by a
Company_ID and Audit and Network are related by CMS_ID. I have A form
Add Customer that adds into the Company and automatically creates a
record in both Audit and Network by putting in a CMS_ID in both. What I
want to do is be able to enforce the refernital integrity between these
tables but when i do it gives an error saying you cannot add, but then
adds the data anyways. The problem I am having is if I change my Company
ID i am getting duplicate records for my CMS_ID (which i can deal with,
i just wont allow changing of the Company_ID. My problem will come in
when I delete a record. I want to specify a Company_ID to delete and be
able to delete all Audits, And Network entries that are related to that
company I want to delete. What do you guys suggest I do?
Thanks in advance

 

[Ken Snell [MVP]]

Your description is a bit confusing to me. How about if you post the fields
that are in the tables, and show what happens when you add a new company and
what is to happen if you delete a company. From what you describe, it seems
as if Audit is a child table to Company, and Network is a child table to
Audit? Which tables are to have referential integrity: Company and Audit?
Audit and Network?

 

[Brant]

Company Has Company_ID, Company_Name, Company_Address; Audit has
Company_ID, Audit_ID, Audit_Date, Audit_Type, CMS_ID; and Network has
CMS_ID, and Network_infomation (which is acctually lots of information
about the network.) There is also a different table Domain which has
CMS_ID and Domain_Info. When I use my add customer Form, the user will
fill in Company_ID,Company_Name, Company_Address and then Automatically
when Company_ID is updated, CMS_ID in all places is updated to a
concatonation of a letter and the company ID. SO if the company_ID is
123, I will create 2 audits with cms_ids N123 and D123. The two problems
are if i want to change 123 to 456 i get N123,D123,N456,D456 all as
audits of Company_ID 456. That I can live with if it is too much of a
problem to fix since i can just not allow changing of a company_ID. The
problem is if I want to delete the company with 456, it deletes the
Company and the Audit, but will leave the Domain and Network records. So
i thought that if i enforced referential integrity between audit and
domain and audit and network that it would delete these fields. Unless
there is another easy way to delete these fields. Thank you

 

[Ken Snell [MVP]]

Your problem is because your CMS_ID is not an atomic data value...it's a
combination of two data values: the company ID and a letter. As such, you
cannot link CMS_ID to Company_ID in any way through referential integrity
because they are not the same values.

Instead of using CMS_ID as you've designed it, you should have two fields in
its place:
Company_ID
AuditLetter

Then you can use referential integrity through the Company_ID field as you
desire.

A query can always combine these two values to present an "audit" ID.

--

Ken Snell
<MS ACCESS MVP>