referential integrity problems with 2 tables

[roger]

I have 2 tables, one courses which has like 35 diffrent
courses that the students can take and then a table which
has all the attendance recorsd for students as theya
ttended/completed these ourses. Ealrier there was a 1-many
relationship and there was referential intergrity set on
i.

The need was that some of the courses were no longer
offered and new ones had to be added. When I tried to
delete the courses it would not let me do that. So what I
did was went to the relationships window....temporraily
diabled the join...deleted the old courses,added the new
courses....and then again made the relationship 1-M but
when I tried to create the refential intergity it said
that will not be possible.

I guess that is fine right...I am figuring that this
happened because there are still some entries in the
attendace table which do not have valide courses any moe
in the cousres table. But going forward a student will be
able to enter/attend only courses that are there on the
list and there is nothing wrong with these right?

 

[Tim Ferguson]

I have 2 tables, one courses which has like 35 diffrent
courses that the students can take and then a table which
has all the attendance recorsd for students as theya
ttended/completed these ourses.

What happened to the Students table?

....

did was went to the relationships window....temporraily
diabled the join...deleted the old courses,added the new
courses....and then again made the relationship 1-M but
when I tried to create the refential intergity it said
that will not be possible.

Presumably you have Attendance records that still point to non-existent
Courses, as you say below.

....

But going forward a student will be
able to enter/attend only courses that are there on the
list and there is nothing wrong with these right?

It's a bit more general than that. Basically, you cannot have a record in
the Attendances table that contains an invalid Courses record: not just at
data-entry time, but for all time. You have a number of choices.

You can delete all the Attendance records for courses that no longer exist.
This will lose all your historic records but perhaps you have other
archives, or you just don't need them anyway.

You can replace all the extinct Course records in order to maintain the
history, but mark them with a NoLongerOffered field so that they don't
appear in combo boxes and so on for new Attendance records to choose from.
Even better might be two YearStarted and YearEnded fields: you can easily
find the active courses WHERE YearEnded IS NULL and so on.

In general, if you find you have to take down all the dbEngine rules that
are there to protect your data, it's usually you who is wrong rather than
the dbEngine! Ref Integrity is about keeping your data meaningful and safe,
so use it.

Hope that helps


Tim F