Cannot enforce referential integrity, but primary keys are set

Hello,

I am trying to link two tables in a database and enforce referential integrity. The first table contains ID information about children, including their location, name, record #, and birth date. The record number repeats within each village, and many children have the same name. As a result, the primary keys for this table are all location related fields, the child's name, record #, and date of birth. The second table will contain information about rounds of vaccines the child has received. This table has the same primary keys plus another one for the "round" field. Ideally during data entry, first, we will check whether a child exists in the ID table. If not, we will add them. Then we will add their info to the immunization table.

I created relationships, but I cannot enforce referential integrity. I already have primary keys, and I checked that the data type in the two tables matches. I would like to set cascade updates as well. Any idea why I can't enforce RI?

Please take a look and let me know what you think! Thanks.

JTT
ghaphisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
Attach a database with these two tables, with no data. Compact and repair first.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
JTT,
<<I created relationships, but I cannot enforce referential integrity. I already have primary keys, and I checked that the data type in the two tables matches. I would like to set cascade updates as well. Any idea why I can't enforce RI?>>
  if this is a "split" database design, make sure your trying to do this in the backend database.  Beyond that, it's hard to say what might be wrong.  What I would try is this:
1. Create a new MDB and then import the two tables.
2. Delete all the data in both tables.
3. Try and create the relationship.
If that works, then it's something with the existing data.  If not, then it's something your doing with the design.  At that point you can upload the DB so I can look at it.
JimD.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<I would like to set cascade updates as well>

I would urge you to carefully consider this option. In a properly designed relational system, there is no reason to enforce cascase Updates, since you should ONLY be relating tables based on a 100% static value (i.e. one that is assigned and will never, ever change). If you cannot guarantee that your relating field value will not change, then you've either (a) your database isn't setup properly or (b) you'd not made the correct choice for your Primary key or your Foreign key.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I would urge you to carefully consider this option. In a properly designed relational system, there is no reason to enforce cascase Updates, since you should ONLY be relating tables based on a 100% static value (i.e. one that is assigned and will never, ever change). If you cannot guarantee that your relating field value will not change, then you've either (a) your database isn't setup properly or (b) you'd not made the correct choice for your Primary key or your Foreign key.>>
  I'm would not agree with that.  It boils down to your definition what is "proper" for relational design.  If we were doing it by the book, natural keys would be the only proper design.  And when using natural keys, cascading updates is a requirement as a primary key can change.  Nothing in relational theory says it cannot.  
 But since we don't go by the book for current designs and use autonumber/ identity columns for keys, then yes, cascading deletes are not required as your key would never change.
JimD.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I guess it would depend on what you define as "the book", but you're certainly correct that it's based on one's opinion/interpretation of the concepts. My definition of proper database design would be a non-changing key, even if it's a surrogate, and enforcing data uniqueness through a different set of factors/columns. I believe that be much much closer to a "proper" database design than using natural keys.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I believe that be much much closer to a "proper" database design than using natural keys.>>
It's only proper in the sense that it works with the computer systems we have in use today.  But it's not in regards to relational theory.  Anything other then what we refer to as a natural primary key simply doesn't do the job it's supposed to do in regards to the theory.
If we had systems capable (performance wise) of supporting natural keys, then we'd be set.  Unfortunately, we don't.
JimD.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<It's only proper in the sense that it works with the computer systems we have in use today>

Very true. I probably would have been closer to accurate had I presented my opinion as "In a properly designed data structure" instead of "In a properly designed relational system".

I'm slowly learning the differences in the methods/terminology/practices and how they interact with one another. I'd welcome any links to sources you consider to be relevant on these subjects.
0
ghaphisAuthor Commented:
I'm using natural keys, which is why I'm concerned about being able to set cascade updates. It's possible that the unique identification information for a person in this database will changed at some point, in which case their records in all tables should be updated accordingly.

I've attached the database with no data. Note that there was the same problem with and without the data, which suggests that there is a structural problem.
IP-Database-v2-EE-250310.mdb.zip
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry for the delay in getting back, but I was traveling yesterday.
I've looked at your DB and we need to make some adjustments in the design.  While you started off in the right direction, you didn't quite end up in the right place.
What I'm looking at is the area table; that really need to be broken down and is one of the reasons that your PK (Pirmary Key) for Children is so un-wieldly.
Before we move onto the children table, I think we need to get this squared away first.  
The area table has:
District
Township
ClinicName
ClinicCode

  When designing a relational database, each type of "thing" should be represented by a table.  Take a look at the last two fields you have; ClinicName and ClinicCode.  Both those have to do with a clinic and not with an "area".   The clinic is *in* a area, but it doesn't describe something about an area.  For example, something like the lat and long of the geographic center
so they need to be in a seperate table, like this:
tblClinics - One record per clinic
ClinicCode - Text - PK
Name - Text - Name of clinic
  Next up is district, township, and area.  I don't understand the relationship between those. ie. does a township always lie within a district, and a area within that?  or are they all independent things?   If the former, you would want something like this:
tblDistricts - One record per district
DistrictID - Autnumber - PK
Name - Text - Name of district
tblTownships - One record per district
TownshipID - Autonumber - PK
DistrictID - Long - Foreign Key to tblDistricts
Name - Text - Name of township
tblAreas - One record per area
AreaID - Autonumber - PK
TownshipID - Long - Foreign Key to tblTownships
tblChildren - One record per child
ChildID - Autonumber - PK
Name - Text
BirthDate - Date/time
RecordNo - Text
AreaID - Long - Foreign Key to tblAreas

  now when I say a child is in a area, by knowing that I know that the area lies in the township of (some name), and that township lies in the district of (some name).   But If they were all independent of one another, then you'd want this:

tblDistricts - One record per district
DistrictID - Autnumber - PK
Name - Text - Name of district

tblTownships - One record per district
TownshipID - Autonumber - PK
Name - Text - Name of township

tblAreas - One record per area
AreaID - Autonumber - PK
Name - Text

tblChildren - One record per child
ChildID - Autonumber - PK
Name - Text
BirthDate - Date/time
RecordNo - Text
DistrictID - Long - Foreign Key to tblDistricts
TownshipID - Long - Foreign Key to tblTownships
AreaID - Long - Foreign Key to tblAreas
 which is what you were going for I believe.   It might also be something in between those two that is required.  For example, "disctricts" may be areas as defined by WHO, "areas" might be assigned by the agency that is doing the imunizations, and "villages" and "townships" both describe a place where someone might live.
  So feed me a few more details on what those things represent and we'll get you squared away.
JimD.
 
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Scott,

<<Very true. I probably would have been closer to accurate had I presented my opinion as "In a properly designed data structure" instead of "In a properly designed relational system".

I'm slowly learning the differences in the methods/terminology/practices and how they interact with one another. I'd welcome any links to sources you consider to be relevant on these subjects.>>
  I posted a reply yesterday, but apparently it didn't make it. :(
  I could point you to the article I wrote on keys as a start, but that certainly would not be a definitive source<g> (Mark Wills thinks I'm nuts), so I'll try and come up with some revelent links.  But if you have the time, you might want to give it a read through:
http://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html

  That BTW was one thing I didn't get out of conference; the chance to talk shop with some folks.  For example, I wanted to pin down Mark on his comments on my article.  Next time we get together, I'd love to bat this around.
JimD.
0
dportasCommented:
>> My definition of proper database design would be a non-changing key, even if it's a surrogate, and
>> enforcing data uniqueness through a different set of factors/columns.

This is a contradiction. If you are enforcing uniqueness on a set of columns with changing values then by definition that IS a key that is changing. There is no reason why key values should not change in a well designed database. In fact it's often highly desirable. Think about a user's login name. It must be unique but there's no reason why the user shouldn't change their name as long as it remains unique.

It's important always to keep in mind that "primary" keys are of no real importance in relational database design. Candidate keys are what make a relational database and it's quite normal for at least some of those candidate keys to change. There is nothing fundamentally "wrong" with such designs.
0
ghaphisAuthor Commented:
Jim,

I followed your suggestions and separated the district, township, and clinic information into three separate tables linked by foreign keys. I hesitate to use an autonumber for each child because the program actually has a record number for each child that repeats in each village. So the data is nested:

district>township>clinic>village>record# of child

The primary keys for the child info table are now districtid, townshipid, clinicid, villageid, record#, and the districtid, townshipid, clinicid, villageid are linked to each separate table with referential integrity enforced.

In the Immunization log form table, there are the same primary key/foreign key fields (districtid, townshipid, clinicid, villageid, record#) and an additional primary key called "round" for each round of vaccination the child receives.

Now I am trying to create a relationship between the child and the immunization tables between the districtid, townshipid, clinicid, villageid, and record# fields and enforce RI, but I still can't. I get the same error message (No unique index found for the referenced field of the primary table). Any idea what may be going on?

JTT
0
hnasrCommented:
Complicated tables.
Please refer to help: Guide to table relationships
I tried to reduce the number of fields, but time contraint limited my attempt.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Any idea what may be going on? >>
Post what you have at this point.
JimD.
0
Vadim RappCommented:
> Think about a user's login name. It must be unique but there's no reason why the user shouldn't change their name as long as it remains unique.

This is actually a counter-example, since in the domain, the true primary key of security object is its SSID, which does not change.

The main practical problem of changing primary key in real life is that whenever you create a relationship, you have to remember to specify auto-update of the key.

The most familiar practical demo of this is the myriad of dead links, for example on Microsoft website. Some site, even this one, refers a page on Microsoft site; several years down the road, it's dead. Auto-update is not always possible, it's not always under your control, and it's not forever. It's much more reliable to have permanent primary key, and everything else derived from it.
0
ghaphisAuthor Commented:
Here is the latest version of the database. Please take a look and see why I can't enforce referential integrity between the immunization and child info tables. Thanks.

JTT
IP-Data-Base-v2-EE-0104010.mdb.zip
0
Vadim RappCommented:
looking at the structure, I think I can make some improvements... please answer these questions:

1. doesn't every village belong to township? in the existing structure, villages are totally independent from districts and townships, is it so indeed?
2. does every child really belong (assigned to) township?
3. if township belongs to district, and child info record belongs to township, then don't refer district in the child info. Refer just the township
4. As I understand, the main table in all this is "immunization log form", this is what you actually have to capture. So, what information you really need to capture? I mean, if the purpose is to remember which child has received which shot (and maybe in which clinic), do you really need to capture township and district as well? since the clinic already identifies the township and then the district, they are always possible to find out from the clinic and don't need to be captured; the only case when you need to capture them is if. for example, township can move in the future to another district, but you still need to know then-current district at the time of each immunization; do you need that really?
5. "child information" already has dateofbirth, no need to capture it in the "immunization form" (unless it's the same as #4 above, and you need to capture then-current DOB at the time of immunization; but it probably can't change, can it?)
6. what is "record" field? is it unique identifier of the child?

0
dportasCommented:
vadimrapp1:
>> true primary key of security object is its SSID, which does not change

My point was actually a bigger one about candidate keys generally. In principle and in practice all keys are equal and "primary" keys are not a special case. So "true[?] primary key" means exactly the same as "a candidate key". It's quite normal for things to have more than one identifier and it's often desirable for at least some key values to change.
0
Vadim RappCommented:
This probably goes somewhat into philosophical area; I'd say, "true" primary key is the criteria by which there's common agreement to identify the item.

In the example of active directory, I can delete userA, and then create new userA. The username will be the same. Is this the same user, or not? from the system's perspective, he is not.

Using this interpretation, the "true primary key" is the identity of the item, so for a particular it will never change, exactly because that's what identifies this particular item.
0
Vadim RappCommented:
so for a particular it will never change
->
so for a particular item it will never change
0
dportasCommented:
The question however is about database design. In the relational database model a candidate key is nothing more or less than a set of attributes. All candidate keys may have changing attributes (more precisely the set of tuples in a relation gets replaced by another set of tuples - that's what we mean by "change"). As far as the world outside the database is concerned, whatever identifier you use could in principle be changed, whether you call it a user name or a surrogate key or whatever. How some application interprets that change depends on the application or consumer of the data - it is never part of the data model itself.

In fact, different users and systems will often have different requirements for what identifies the item in question. To take your example, Active Directory may identify a user account by its "ID" but the AS users never will. As far as users are concerned an account is identifiable by a name or other attributes. So the notion that one key is better than another is highly subjective. It's a matter of perspective and user requirements rather than any fundamental difference.

The idea that you "must" have a "primary" key that is somehow special or unchanging is ultimately too rigid for the real world. The really important questions are: What keys do I need? How will the keys be used? Whether you choose to refer to one or more of those keys as "primary" is entirely unimportant and arbitrary.
0
Vadim RappCommented:
> The idea that you "must" have a "primary" key that is somehow special or unchanging is ultimately too rigid for the real world.

I'd say, in the real world this is exactly what is being done. Whenever someone is going to enumerate something, some identity is established, with the idea that different identity means different item, be it a person in IRS database, order in the electronic store, or question # 25510303 on this site.

Once such identity has been established, it usually becomes the only one being used. Of course this does not prevent from having multiple unique indexes in the database, or using secondary ways to identify the items ("you don't remember your order number? then tell me your name and phone number and the date of the order, and we will find it"), but still, the very concept of the identity is usually associated with one set.

> The really important questions are: What keys do I need?

Absolutely, and in the real life it gets translated into "how do I identify my items".

This dilemma is actually the one between pure relational approach (which appears to be your platform), and object-orineted approach, where "Primary keys should be immutable, that is, not change until the record is destroyed." (http://en.wikipedia.org/wiki/Unique_key ) - which further says this: "Due diligence should be applied when deciding on the immutability of primary key values during database and application design. Some database systems even imply that values in primary key columns cannot be changed using the UPDATE SQL statement".

So it's really up to the developer/data architect. Different developers may have different views, but I think, when considering the data model, one needs to keep in mind the whole solution, rather than purely relational-database component of it.
0
ghaphisAuthor Commented:
The feedback helped me restructure the database but didn't solve the problem I initially reported - no one mentioned that I needed a single index in the child information table after I reposted the database.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
nuts, I was just getting back to this.  The next stpe was to get into your key setup, but I wanted to make sure we got to the right place with the design first.
I will download your second DB and have a look at the structure anyway.
JimD.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK I've looked at this and this still needs quite a bit of work. I think if you stick with the current design, your going to have issues
Please look at the attached screen shot of the relationships. I've cleaned up the District, Township, and Village tables as a starting point.
Looking back at the comments, I don't see where you ever answered how these things are related to one another. What I need to know is:
1. Will a town always lie within a single district?
2. Does a village always lie within a district or a town?
3. For your purposes, what's the difference between a town and a village?
4. Where can a clinic be located?
Answer those questions and I'll modify the design and then will move onto the children and the immunization tables. And we'll get this cranked out today for sure. I'm in the office all day today (was traveling yesterday, which is why you didn't hear from me).
JimD.

Window-Capture.jpg
0
Vadim RappCommented:
For the purposes of the KB, here's one way to do it. Assuming that village does not belong to the township, and we don't follow child's belonging to the clinic - it may be useful for some other purposes, but not here; so we simply capture the clinic at the time of immunization round. Note also new table "immunizations".


Capture-04-02-00001.png
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.