Referential Integrity

[Guest]

I obviously do not understand referential integrity.

I have three tables:
Email
Employee
Training

The Email table has a master list of all employee e-mail addresses.
The Employee uses the e-mail address as its key.
The Training table uses the e-mail address from the Employee table as one of
its fields.

The relationship between Email and Employee tables enforces referential
integrity.
The relationship between Employee and Training tables enforeces referential
integrity.

I desire to change someone's e-mail address in the database. It was my
thought, obviously incorrect, that with referential integrity, I would change
the address in the Email table and it would flow through all the related
tables. That isn't the case.

The way I have things configured, I cannot change an employee's e-mail
address. I know I can temporarily delete the integrity, make the change,
change all instances, and then put the integrity back in. But that obviously
is not how it is supposed to be done.

So, where have I gone wrong? What do I need to do to have a change flow
throughout all related tables?

I appreciate any help you can offer.

 

[Guest]

Hi, Shoelaces.

You do not mention what the primary key of Training is. I presume it is
something other than the email address. However, your table design has two
areas that could be improved.

Email need not be a separate table; it has a one-to-one relationship with
Employee, and could therefore simply be a field in that table.

An email address is cumbersome as a primary key, whose sole purpose is to
provide a unique identifier for each record. Most developers prefer a simple
autonumber primary key (no code required) or a numeric one that they
increment, due to limitations by their businesses they work in (code
required).

No fields from one table should be duplicated in another except a foreign
key. Linking the primary key in one to the corresponding foreign key in the
other establishes the relationship on which referential integrity can be
enforced.

Employees
---------------
EmployeeID AutoNumber or Integer (Primary Key)
EmpFName Text
EmpLName Text
Phone Text
BirthDate Text
Email Text

Training
---------------
TrainingID AutoNumber (Primary Key)
EmployeeID Integer (Foreign Key to Employees)
Course
HoursEarned
Grade
.....etc.

Hope that helps.
Sprinks