Referential Integrity



I have three tables that I am trying to build relationships for:
Employee Table: which has Name,Address, and EquipNum with EmpID as the
primarty key.
Equipment Table: which has CompCode with EquipNum as its primary key
Equipment Rate Table: which has BillRate and OTBillRate with CompCode as its
primary key
I linked the CompCode on the Equipment Rate Table to the CompCode on the
Equipment Table and applied Referentially Integrity.
But when I tried to link the EquipNum on the Equipment Table to the EquipNum
on the Employee Table, it won't allow me to Enforce Referential Integrity.

I am trying to build a query that shows the employees name with what
Equipment number they have and the rate that applies to it.

Why can't I enforce referential Integrity


You cannot enforce referential integrity when you have data in the tables
that violate integrity. Review your data that violates the referential
integrity, upate it and then enforce the integrity.


Duane Hookom

I'm not sure why you have EquipNum in the Employee table. Can an employee
have only one piece of equipment? Does each employee always have a piece of

Normally I would expect to see an EmployeeEquipment table with EmpID and
EquipNum as a two field unique index.


Lets say you have a table with a column called Gender and in your Gender
table there were two records M and F. If you had a record in your table with
a value of X, you would not be able to enforce the relationship until you had
either deleted this record or changed the value to M or F.



Blissfully said:
Employee Table: which has Name,Address, and EquipNum with EmpID as the
primarty key.

Should not have EquipNum, unless "each employee can have at most one
piece of equipment".

Equipment Table: which has CompCode with EquipNum as its primary key

The PK sounds right. Not sure what CompCode would be, so can not
comment on that. Surely there are more things that you'd like to record
for each pience of equipment? SerialNumber, DatePurchased, etc. etc.
Those would go in this table.

Equipment Rate Table: which has BillRate and OTBillRate with CompCode as its
primary key

Can't comment on this without knowing what those fields are. It's like
saying, "I have a table T100 with fields F100, F101 (pk) and F102" -
no-one could comment on that.

IMHO you should clarify the table structures before you start on the
relationships. Tables (especially PKs) first, then the relationships
will fall out naturally. But if the PKs are wrong, you'll just go
'round & 'round in circles trying to get the relationships right.



"Blissfully Ignorant" <[email protected]>
wrote in message
What would violate the integrity? What are the rules?


<major db discussion>

My apologies of you already know any and/or all of this.

Part of the trouble about understanding relationships could come
from the fact that Relation, Relations, and Relationship(s) have
multiple meanings in regards to, "Relational Databases."

One meaning, is that the underlying theory behind Relational
Databases is a branch of math called Relational Algebra (invented by
Dr. Codd). This branch of math works on "sets" of data. A "set" in
Relational Algebra is called a "relation" (don't ask me why . . .).
Then, some people in various companies (IBM, Oracle, etc.) took this
branch of math and made it into the precursors of the Relational
Databases apps we have today (they grabbed "relational" off of
Relational Algebra and stuck it in front of "database", and voila,
we have "Relational Databases").

Another meaning is that "Relation" is the technical name of what
Relational Databases call "Tables". (And just to be complete, the
technical name of "Row" is "Tuple".)

Now, in order to run our databases, we have something that goes by
the technical term, Referential Integrity. This states that if we
start with table called Items, that has a Primary Key of UPC, and we
have table called Pricing, that has a primary key of UPC and
PricingDate. Whatever happens, we don't want to have a row in the
Pricing table that has an UPC value that doesn't appear in the Items
table. Why? Because we'd have prices in Pricing for items no one
could in the company could identify (there being nothing in Items to
identify the item). Relational Databases use internal management,
usually indexes, to *enforce* a "rule" that automatically prevents
users from deleting any row in Items . . . *when there is still any
row* . . . in Pricing with the UPC about to be deleted from Items.
This process, this "enforcement" of the "rules", is called
Referential Integrity.

Now, in MS Access, you open up the "Relationships" window, and make
some table sub-windows appear on it. Then you drag fields back and
forth between the table sub-windows and establish nice little lines
that go from table to table, and MS Access calls these lines
"Relationships" (and a lot of other people do, too). In this case,
an MS Access "Relationship" is the method whereby "Referential
Integrity" is set up. It should be noted that MS Access uses the
word "relationship" in completely different ways than other database
products, and differently from Data Modeling (more on that in a
second), but is so pervasive on the PC desktop world, that
terminology creep has taken place, causing the MS Access version of
"relationship" to be thought of by many as "what real relationships
are in databases".

To further confuse the issue (yes, there's more), Data Modeling, the
process where ideas about things (entities) are organized into
understandable formats, like big charts covered by boxes with lines
running between them (rather remarkably like the boxes and lines in
the MS Access Relationships window), also uses the concept of
"relationships". Except in Data Modeling, a "relationship" is an
actual box on the chart.

Data Models themselves are built to help database designers create
good databases. When all the aspects of the chart (there can be
many versions and detail levels) are completed, and I'm skipping
over a lot of stuff here for simplicity, the chart is turned over to
the database designers, and they go to work.

The boxes on a Data Modeling chart represent "entities", or the
people, places, things, or knowledge that are being described on the
chart. A "relationship", then, is a form of knowledge because it
*tells* us something. Therefore, it gets a box on the chart. And
here's another one, the lines on a Data Model chart? They represent
something called Cardinality. Cardinality is one to one, one to
many, many to many, recursive (Bill of Materials), etc. To yet
further confuse things, the word "relationship" *frequently*
replaces "Cardinality" even when experts are discussing the
situation. It's important, because a table of a concrete entity
(like a Book) can have a many to many "cardinality" with a
"relationship", an abstract piece of knowledge. I told you it would
be confusing. If a Book can be checked out of library by many
people, the book does not have a one to many cardinality with
people, there is, rather, a relationship between books and people,
and books have a cardinality with that relationship, and people have
a cardinality with that relationship (we'll call it a CheckedOut

Concrete Entity: Books
Concrete Entity: People

Abstract Entity: CheckedOut

Both Books and People can appear in Checked-Out over an over again,
as the same person checks out and reads the same book over and over
again. The check out date will be the attribute ("CheckedOutDate")
of CheckedOut that helps define each row. The primary key would be,
BookID, PeopleID, and CheckedOutDate. The "relationship" between
books and people is that people can check the books out of the
library. The two cardinalities from the two concrete entities to
the one abstract relationship are the one-to-many (etc.) part of it.

Yes, cardinality is *rarely* discussed by MS Access users. But it
is still important to realize that it exists. It is further
necessary to realize that what MS Access "calls" a "relationship" is
really called something else by a large fraction of Data Modelers
and Database Designers. It's important because you have to realize
that a real-world entity, a "bit of knowledge", may be a
"relationship" between those entities and that "relationship" may be
described in the database by a Table. And in MS Access, the
cardinality between the tables is called a "relationship", as well.

Don't ask me why the MS Access designers decided to rob an already
massively overused term and apply it the way they did, but they did,
and knowing about it can be quite helpful.

Now, jumping out of the Data Modeling picture, let's go a little
further into Database Design. There are three main rules of good
table design for Relational Databases. They are Called First Normal
Form, Second Normal Form, and Third Normal Form (often abbreviated
1NF, 2NF, and 3NF). There's also BCNF, 4NF, and 5NF (and even a
proposed 6NF). These rules, when followed, among other things, make
establishing RI easier (they make the whole DB Design process
easier, most of the time). Look up these rules on the internet (you
may already have, I am sliding away from the original topic) for a
set of rule that, if followed, will allow you to build databases
that will help you store the data in a way best suited for this kind
of database. This is important because unless the data is stored
correctly, it can become very difficult to get the answers you want.

</major db discussion>


Database Normalization:



MySQL's website:



Very Advanced:

University of Texas:
(I quite like this whole site, since it has a handy menu on the
describing many important aspects of database normalization and


Chris O.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question