I have about 5 tables that I cannot enforce referential integrity
on... 3 of them have a common field, but the error messages I am
receiving are way above my head technically. Any suggestions? Or
even documentation on relationships (from the very beginning) would
help a lot!
Thanks
Mindy
Mindy,
Referential Integrity is a feature offered by relational databases.
In MS Access, referential integrity (RI) is enforced by a Relationship object.
When you--the database designer--establish a Relationship between two tables, it is
usually done so that you can be sure that values in a column in the first table are the
only values used in a column of the second table, and that values in the second column
cannot exist when there are no matching values in the first column.
From the first table, the most common column to use for a Relationship would be the
Primary Key.
From the second table, the most common column to use for a Relationship would be a column
that was used to store values from a Primary Key column in the first table. This column
is called a Foreign Key (because it is really a key from another "foreign" table).
A Relationship object establishes a hidden index on the column in the second table, and
there must be a Primary Key or Unique Index on the column in the first table.
(This works on multiple columns as well.)
Why does anyone want to establish RI?
If you are a grocery retailer, at the simplest level you probably have an Items table and
a Prices table.
Items
ItemID -- Primary Key
ItemName
Prices
PriceID -- Primary Key
ItemID -- Foreign Key
Price
If you delete and item row from Items, and you have prices for those items in Prices, you
suddenly have a situation where no one can determine what any of those prices were
attached to (the item is gone).
Nobody wants that.
When a Relationship object exists between ItemID in Items and ItemID in Prices, the
database automatically checks to make sure this can't happen. If you try to delete an
ItemID value from Items when there are any of those ItemID values left in Prices, the
query will fail. If you try to insert a row into Prices with and ItemID value that
doesn't already exist in ItemID in Items, that query will fail.
This keeps your database neat and tidy.
You can use CASCASE DELETE and CASCADE UPDATE when setting up a Relationship.
CASCADE DELETE makes it so that if you delete a row in Items, and there are any prices in
Prices for it, the rows in Prices are automatically deleted.
CASCADE UPDATE makes it so that if you change an ItemID value in Items, any occurrences of
that value in Prices are also automatically updated.
There is no such thing as CASCADE INSERT.
----------------
You want to match every Primary Key's Foreign Key (Items.ItemID and Prices.ItemID type
combination) with a Relationship object throughout the database.
----------------
It is possible to establish RI constraints on columns other than Primary Key/Foreign Key
pairs, but it is fairly rare, except for recursive relationships, which are well beyond
the scope of our discussion.
----------------
Based on your further description in the other branch of this topic, I'm not sure that RI
has anything to do with your query's error.
IMO this: "The SQL statement could not be executed ebcause it contains ambiguous
outer joins. To force one of the joins to be performed first, creat a
separate query that performs the first join and then include that
query in you SQL statement."
Usually means that there are column (or table) names in the query that cannot be resolved
(the database can't figure out what they belong to).
This is usually, but not always, the result of one or more missing table qualifiers (like
"Items.ItemID, Prices.ItemID" is fully qualified, but "ItemID, ItemID" isn't.).
More cannot be said (by me) without viewing your SQL. (Open your query in Design View,
then View > SQL View, and then copy and paste post the code.)
Sincerely,
Chris O>
