Relationship Help Needed

[Charles E Finkenbiner]

Hi All,

I am new to Access and 'Referential Integrity' is a new concept for me.
In order to understand it better I started working on some sample
tables with old data I had. I thought I had understood it fairly well
until I ran into a problem. Let me explain my table setup, hope this is
not too long.

Table/Fields:
Countries:
2Code - Text - Primary and Unique key
3Code - Text - Not required
Number - Text - Not required, Input mask 000
Name - Text - Not required

States:
Code - Text - Primary and Unique key
CountryCode - Text - Not required
Name - Text - Not required
Code and CountryCode can not be combined into 1 primary key because APO
and FPO codes to not have a country code assigned to them, only a state
code.

Counties:
ID - AutoNumber - Primary and Unique key
StateCode - Text - Required, no zero length
Name - Text - Not required

Cities:
ID - AutoNumber - *
CountyID - Number - *
Name - Text - Not required
* Both fields are joined together as a Primary and Unique key

ZipCodes:
ID - AutoNumber - *
CountyID - Number - *
CityID - Number - *
ZipCode - Text - Not required, Input mask 00000#9999
* All 3 fields are joined together as a Primary and Unique key

Now I want to setup one to many 'Referential Integrity', including
'Cascade Update and Delete', between all 5 tables.

Countries to States
2Code -----> CountryCode - OK

States to Counties
Code -----> StateCode - Failed

Counties to Cities
CountyID -----> CountyID - OK

Cities to ZipCodes
CityID -----> CityID - OK

So, on the 'Relationships' page I have 5 tables in a row, each linking
to the next.

Can someone please explain to me why 'Referential Integrity' will not
work between the States and Counties tables? Yes, I can setup a normal
relationship but them the 'Cascade Update and Delete' will be broken and
not travel down to the remaining tables.


Thanks for any and all help,

Charles

 

[Duane Hookom]

I would expect the relationship between Counties.StateCode and States.Code
to allow referential integrity.

I would seriously consider renaming your fields to never name a field name
since every object in Access has name property. You also have a text field
named number which I would find confusing. I would also change the fields
names of ID and Code to something a bit more descriptive.

I don't know why you would join an autonumber field with another to make a
primary key. An autonumber field is always unique by itself. You can create
other unique indexes on other fields if you feel this is necessary.

 

[Vincent Johns]

I don't have time to duplicate your entire setup, but I noticed a couple
of things which may help...

Hi All,

I am new to Access and 'Referential Integrity' is a new concept for me.
In order to understand it better I started working on some sample
tables with old data I had. I thought I had understood it fairly well
until I ran into a problem. Let me explain my table setup, hope this is
not too long.

Table/Fields:
Countries:
2Code - Text - Primary and Unique key
3Code - Text - Not required
Number - Text - Not required, Input mask 000
Name - Text - Not required

States:
Code - Text - Primary and Unique key
CountryCode - Text - Not required
Name - Text - Not required
Code and CountryCode can not be combined into 1 primary key because APO
and FPO codes to not have a country code assigned to them, only a state
code.

Do you care? It looks as if

 is already unique.  It's not made
any more unique by having other fields attached.  Just copy its value to
the foreign-key field of other tables linking to it.
[QUOTE]
Counties:
ID - AutoNumber - Primary and Unique key
StateCode - Text - Required, no zero length
Name - Text - Not required

Cities:
ID - AutoNumber - *
CountyID - Number - *
Name - Text - Not required
* Both fields are joined together as a Primary and Unique key[/QUOTE]

Or you can use [ID] as the key.
[QUOTE]
ZipCodes:
ID - AutoNumber - *
CountyID - Number - *
CityID - Number - *
ZipCode - Text - Not required, Input mask 00000#9999
* All 3 fields are joined together as a Primary and Unique key[/QUOTE]

Again, it looks as if [ID] can be used by itself.  When I set up tables
like this, though, I usually rename the [ID] field to reflect the table
name, in this case to something like [ZipCodesID].  That makes it more
obvious, when I link to it in other tables using that same field name
(but as a foreign key), which table I'm linking to.  And, if the field
names match, even if you haven't defined the relationship in your
Relationships window, often Access will link the tables automatically
when you're defining queries, saving you the trouble of doing that in
Query design view.

 

[Charles E Finkenbiner]

Hi,

I had searched past posts before I posted my question. I saw previous
posts about the field names syntax I used and the confusion it might
cause with Access properties/methods. After my post I corrected all the
field names, no problems anymore.

As for joining fields to create a primary index. Since I planned on
using ADO and the index/seek commands I thought it would be faster to
have/use an index. I have since written a sample script using ADO and
the MS Jet provider in order to test but I get an error message that the
provider does not support index and seek. I could have written the code
wrong because I was sure I read that JET did support these 2 commands.

I will redesign my tables in order to take advantage of both your
suggestions.


Thanks for the help,

Charles

I don't have time to duplicate your entire setup, but I noticed a couple
of things which may help...

Hi All,

I am new to Access and 'Referential Integrity' is a new concept for
me. In order to understand it better I started working on some sample
tables with old data I had. I thought I had understood it fairly well
until I ran into a problem. Let me explain my table setup, hope this
is not too long.

Table/Fields:
Countries:
2Code - Text - Primary and Unique key
3Code - Text - Not required
Number - Text - Not required, Input mask 000
Name - Text - Not required

States:
Code - Text - Primary and Unique key
CountryCode - Text - Not required
Name - Text - Not required
Code and CountryCode can not be combined into 1 primary key because
APO and FPO codes to not have a country code assigned to them, only a
state code.

Do you care? It looks as if

 is already unique.  It's not made
any more unique by having other fields attached.  Just copy its value to
the foreign-key field of other tables linking to it.
[QUOTE]
Counties:
ID - AutoNumber - Primary and Unique key
StateCode - Text - Required, no zero length
Name - Text - Not required

Cities:
ID - AutoNumber - *
CountyID - Number - *
Name - Text - Not required
* Both fields are joined together as a Primary and Unique key[/QUOTE]


Or you can use [ID] as the key.
[QUOTE]
ZipCodes:
ID - AutoNumber - *
CountyID - Number - *
CityID - Number - *
ZipCode - Text - Not required, Input mask 00000#9999
* All 3 fields are joined together as a Primary and Unique key[/QUOTE]


Again, it looks as if [ID] can be used by itself.  When I set up tables
like this, though, I usually rename the [ID] field to reflect the table
name, in this case to something like [ZipCodesID].  That makes it more
obvious, when I link to it in other tables using that same field name
(but as a foreign key), which table I'm linking to.  And, if the field
names match, even if you haven't defined the relationship in your
Relationships window, often Access will link the tables automatically
when you're defining queries, saving you the trouble of doing that in
Query design view.
[QUOTE]
Now I want to setup one to many 'Referential Integrity', including
'Cascade Update and Delete', between all 5 tables.

Countries  to  States
2Code -----> CountryCode - OK

States  to  Counties
Code -----> StateCode - Failed

Counties   to    Cities
CountyID -----> CountyID - OK

Cities  to   ZipCodes
CityID -----> CityID - OK

So, on the 'Relationships' page I have 5 tables in a row, each linking
to the next.

Can someone please explain to me why 'Referential Integrity' will not
work between the States and Counties tables?  Yes, I can setup a
normal relationship but them the 'Cascade Update and Delete' will be
broken and not travel down to the remaining tables.


Thanks for any and all help,

Charles[/QUOTE][/QUOTE]

 

[Charles E Finkenbiner]

Hi,

Ok, I redesigned all the tables and now have 'Referential Integrity'
working across all 5 tables. Thanks for the help.

1 question, I have 'assumed' that RI only works from a top to down
direction. So, if I delete a city only the associated zip codes will be
deleted. Am I correct? If I delete only 1 zip code then that is all
that gets deleted, right?


Thanks for all the help,

Charles

Hi,

I had searched past posts before I posted my question. I saw previous
posts about the field names syntax I used and the confusion it might
cause with Access properties/methods. After my post I corrected all the
field names, no problems anymore.

As for joining fields to create a primary index. Since I planned on
using ADO and the index/seek commands I thought it would be faster to
have/use an index. I have since written a sample script using ADO and
the MS Jet provider in order to test but I get an error message that the
provider does not support index and seek. I could have written the code
wrong because I was sure I read that JET did support these 2 commands.

I will redesign my tables in order to take advantage of both your
suggestions.


Thanks for the help,

Charles

I don't have time to duplicate your entire setup, but I noticed a
couple of things which may help...

Hi All,

I am new to Access and 'Referential Integrity' is a new concept for
me. In order to understand it better I started working on some
sample tables with old data I had. I thought I had understood it
fairly well until I ran into a problem. Let me explain my table
setup, hope this is not too long.

Table/Fields:
Countries:
2Code - Text - Primary and Unique key
3Code - Text - Not required
Number - Text - Not required, Input mask 000
Name - Text - Not required

States:
Code - Text - Primary and Unique key
CountryCode - Text - Not required
Name - Text - Not required
Code and CountryCode can not be combined into 1 primary key because
APO and FPO codes to not have a country code assigned to them, only a
state code.

Do you care? It looks as if

 is already unique.  It's not made
any more unique by having other fields attached.  Just copy its value
to the foreign-key field of other tables linking to it.
[QUOTE]
Counties:
ID - AutoNumber - Primary and Unique key
StateCode - Text - Required, no zero length
Name - Text - Not required

Cities:
ID - AutoNumber - *
CountyID - Number - *
Name - Text - Not required
* Both fields are joined together as a Primary and Unique key[/QUOTE]



Or you can use [ID] as the key.
[QUOTE]
ZipCodes:
ID - AutoNumber - *
CountyID - Number - *
CityID - Number - *
ZipCode - Text - Not required, Input mask 00000#9999
* All 3 fields are joined together as a Primary and Unique key[/QUOTE]



Again, it looks as if [ID] can be used by itself.  When I set up
tables like this, though, I usually rename the [ID] field to reflect
the table name, in this case to something like [ZipCodesID].  That
makes it more obvious, when I link to it in other tables using that
same field name (but as a foreign key), which table I'm linking to.
And, if the field names match, even if you haven't defined the
relationship in your Relationships window, often Access will link the
tables automatically when you're defining queries, saving you the
trouble of doing that in Query design view.
[QUOTE]
Now I want to setup one to many 'Referential Integrity', including
'Cascade Update and Delete', between all 5 tables.

Countries  to  States
2Code -----> CountryCode - OK

States  to  Counties
Code -----> StateCode - Failed

Counties   to    Cities
CountyID -----> CountyID - OK

Cities  to   ZipCodes
CityID -----> CityID - OK

So, on the 'Relationships' page I have 5 tables in a row, each
linking to the next.

Can someone please explain to me why 'Referential Integrity' will not
work between the States and Counties tables?  Yes, I can setup a
normal relationship but them the 'Cascade Update and Delete' will be
broken and not travel down to the remaining tables.


Thanks for any and all help,

Charles[/QUOTE][/QUOTE][/QUOTE]

 

[peregenem]

I don't know why you would join an autonumber field with
another to make a primary key. An autonumber field is
always unique by itself.

To find out why, try looking at things from a different perspective.

What does PRIMARY KEY do that NOT NULL UNIQUE doesn't? Well, a table
may have multiple NOT NULL UNIQUE constraints but may only have one
primary key.

Why are we only allowed one PRIMARY KEY per table? Allow me to quote me
old pal Joe Celko:

"In the first papers that Dr. Codd wrote, he talked about
candidate keys -- all the possible keys that exist in a table. Then
you
were to pick one of them to be called the PRIMARY KEY.

"Frankly, this was a hold-over from the days of sequential files --
hey,
Dr. Codd did not come up with the whole RDBMS model all at once. When
we did EDP (Electronic Data Processing) on magnetic tapes in the 1950's

and 1960's, the typical application merged tapes together, so both the
master tape and the transaction tapes had to be sorted on the same key
(account numbers, or whatever). You do not do random seeks on a
magnetic tape.

"Very quickly, The Good Doctor realized that a key is a key, and giving
a
special name to one of them changes nothing about its nature. Primary
keys were then dropped from database theory.

"However, System R and SQL software had been built on top of old file
systems and Dr. Codd's first papers. The PRIMARY KEY was implemented
using the existing keys and indexing methods in the old file systems.
And it has stayed there since.

"Some SQL systems assumed that the PRIMARY KEY would be the preferred
access path and optimized for it."

This last sentence is significant. The question now is: What special
meaning was given to PRIMARY KEY in the Jet implementation of SQL? To
cut a long story short, I'll give you the answer: clustered indexes
i.e. physical ordering on disk. You can only have one physical order
(think paper copy telephone directory: its physical order is fixed)
hence only one PRIMARY KEY. In Jet there is no other way of specifying
the physical ordering for than to use PRIMARY KEY.

If you are using PRIMARY KEY to merely mean NOT NULL UNIQUE then you
are under-utilizing it at best. If you designate a sole autonumber as
PRIMARY KEY you are certainly using it incorrectly because an
incrementing integer (worse, random GUID) makes for a lousy physical
order (think paper copy telephone directory ordered on telephone number
when your primary usage is to retrieve data by last name then first
name). Choosing a bad PRIMARY KEY can result in placing a performance
hit on your database.

If you decide (and I urge you to resist doing so) to use an autonumber
(ID) to force your rows to be unique for uniqueness' sake where you
have no natural key, then put it last in your PRIMARY KEY definition
and put the columns for your clustered index in appropriate order first
e.g.

PRIMARY KEY (last_name, first_name, ID)

This way, the physical order for the table will be rebuilt in last_name
then first_name order with ID merely to satisfy the uniqueness
requirement.

If you are using autonumber as an artificial/surrogate key on
performance grounds e.g. on the basis that a compound natural key is
less efficient for table joins etc, then NOT NULL UNIQUE is sufficient.
But to use an autonumber for efficiently then take a performance hit by
making it PRIMARY KEY makes no sense!

 

[John Vinson]

1 question, I have 'assumed' that RI only works from a top to down
direction. So, if I delete a city only the associated zip codes will be
deleted. Am I correct? If I delete only 1 zip code then that is all
that gets deleted, right?

That is correct. Deletions flow down, not up.

John W. Vinson[MVP]

 

[peregenem]

Since I planned on
using ADO and the index/seek commands I thought it would be faster to
have/use an index. I have since written a sample script using ADO and
the MS Jet provider in order to test but I get an error message that the
provider does not support index and seek.

Instead use the Field object's Optimize property:

http://msdn.microsoft.com/library/d...en-us/ado270/htm/mdprooptimizepropertyrds.asp

With will create an index to be used with the recordset's Filter
property etc.

 

[Charles E Finkenbiner]

Hi,

Instead use the Field object's Optimize property:

http://msdn.microsoft.com/library/d...en-us/ado270/htm/mdprooptimizepropertyrds.asp

With will create an index to be used with the recordset's Filter
property etc.

Thanks for the pointer.


Charles