Index problem when creating a relationship

[Tony Wainwright]

When linking 2 tables together in a one to many relationship I get the following error message when I try to enforce referential integrity.
'No unique index found for the referenced field of the primary table'
The primary table has 5 fields as it's primary key.
Any ideas anyone

Tony

 

[Rick Brandt]

When linking 2 tables together in a one to many relationship I get the

following error message when I try > to enforce referential integrity.

'No unique index found for the referenced field of the primary table'
The primary table has 5 fields as it's primary key.
Any ideas anyone

Did you use all five of those fields in the relationship?

 

[Tony Wainwright]

Yes I did Rick

following error message when I try > to enforce referential integrity.

Did you use all five of those fields in the relationship?

 

[Robert]

-----Original Message-----
When linking 2 tables together in a one to many

relationship I get the following error message when I try
to enforce referential integrity.

'No unique index found for the referenced field of the primary table'
The primary table has 5 fields as it's primary key.
Any ideas anyone

Tony

Hi Tony,
I believe that your problem lies in the fact that your
primary table has duplicate data in the linking field.
Make sure you don't have any duplicates in your index
field. As the error stipulates "No unique index found".
Also, clear both tables before doing your relation.
Let me konw if this helps!

Best regards
Robert

 

[TC]

Not to answer your question, but, 5 fields is unusual as a primary key. What
does a row in that table, represent? What are the 5 fields in that primary
key?

TC

 

[Tony Wainwright]

Hi Robert and thanks,

I've cleared all data from the tables and also removed all indexes from both
the Primary and Related Tables apart from the Primary Key. This has not
changed anything.

I know it may seem unusual to have 5 attributes as a Primary key, so I'll
explain the logic. The database is for tracking learners reviews and
progress. This is an existing db. I have been asked to include various
other functionality such as tracking the NVQ's Key Skill and Further
Education for the learners.
Primary Key of Tables
================
tblCandidate => PK = CandidateID
tblCourse => PK = CandidateID+QualificationID+Scheme
tblFE => PK= CandidateID+QualificationID+Scheme+CourseTitle
tblFEModule => PK =
CandidateID+QualificationID+Scheme+CourseTitle+ModuleTitle
tblFEModuleExam => PK =
CandidateID+QualificationID+Scheme+CourseTitle+ModuleTitle+ResitNo

Each Candidate can undertake many courses. For each course a candidate
takes they may undertake 0 to many FE courses. For Each course they take
there will be 1 or more modules to that course. For each module they may
take the exam a number of times. The problem I am having is in creating the
relation ship between tblFEModule and tblFEModuleExam. I can create the
relationship, however when I try to enforce referential integrity I get the
error message

Tony

 

[TC]

I think there may be some misunderstanding of primary keys & foreign keys in
your table design.

For example, if candidates take courses, this is how it would normally be:

tblCandidate
CandidateID (PK)

tblCourse
CourseID (PK)

tblCandidateCourse
CandidateID ( composite )
CourseID (primary key )

tblCandidate stores values for the candidate (and ONLY the candidate) -
name, gender, date of birth etc.;

tblCourse stores values for the course (and ONLY the course) - title,
instructor, start date etc.;

tblCandidateCourse has two purposes. First, it indicates which candidates
are on what courses. Second, it stores values for that specific candiaet on
that specific course; perhaps, enrollment date.

IMO, CandidateID should >not< be in the course table. This violates the
principle that the course table is for values that pertain to the course AND
ONLY the course. This is things like course title; not things like candidate
ID or IDs.

HTH,
TC

 

[Tony Wainwright]

Sorted it. Deleted the 2 tables and relationships and re-built them. Don't
know why this happened but it seems cured.

Just out of interest TC - this is the criteria for that part, how would it
normally look?

Each Candidate can undertake many courses. For each course a candidate
takes they may undertake 0 to many FE courses. For each FE course they take
there will be 1 or more modules to that FE Course. For each module they may
take the exam a number of times.

That is tblCandidate to tblCandidateCourse to tblFE to tblFEModule, to
tblFEModuleExam

 

[TC]

Hi Tony

Glad you got it sorted. Just a suggestion: when you reply to a post, please
retain the text of the post you are replying to. Otherwise, if the previous
posts have disappeared from the reader's newsserver, he will not remember
what you are talking about! (certainly not full details). So I've re-added
the previous text, at the bottom of this post.

Each Candidate can undertake many courses.

We already support this with the structure described previously:

tblCandidate
CandidateID (PK)

tblCourse
CourseID (PK)

tblCandidateCourse
CandidateID ( composite )
CourseID (primary key )

For each course a candidate takes they may undertake 0 to many FE courses.

To answer that, you'd need to explain the real-world relationshiop between
"normal" courses (ie. those described in tblCourse) and "FE" courses. What
exactly is an FE course, compared to a normal course? Would tyou envisage FE
courses being in tblCourse?

For each FE course they take there will be 1 or more modules to that FE

Course.

If different FE courses have different mixtures of a common pool of modules;
that is, if a particuar module might be used by one or more FE courses, we
have:

tblModule
ModuleID
module description, author etc.

tblFECourseModule *
FECourseID ( composite ) *
ModuleID ( primary key)

But if a module was never shared between FE courses - ie. the modules for
each FE course were entirely distinct - this would do:

tblFECourseModule *
FECourseID ( composite ) *
ModuleID ( primary key)
module description, author etc.

And if FE courses were really just special kinds of "normal" courses, you
would add a "this is an FE course!" flag to tblCourses, and remove the two
characters "FE" from each of the 4 asterisked lines above.

For each module they may take the exam a number of times.

Another classic "one to many" relationship.


Tony, as you can see from all this, you need some precise information about
the "real world" relationships between the bvarious entities, before you can
design the tables. For example, it's way not enough to say that there are
courses, & there are FE courses. You need to clarify the exact "real world"
relationship between those two types of courses. I'm not saying this to
"have a go" at you - just to point out the level of detailed thinking that
is involved >before< you start designing the table structures.

HTH,
TC

Sorted it. Deleted the 2 tables and relationships and re-built them. Don't
know why this happened but it seems cured.

Just out of interest TC - this is the criteria for that part, how would it
normally look?

Each Candidate can undertake many courses. For each course a candidate
takes they may undertake 0 to many FE courses. For each FE course they take
there will be 1 or more modules to that FE Course. For each module they may
take the exam a number of times.

That is tblCandidate to tblCandidateCourse to tblFE to tblFEModule, to
tblFEModuleExam

== RESTORED SNIPPED TEXT ===
I think there may be some misunderstanding of primary keys & foreign keys in
your table design.

For example, if candidates take courses, this is how it would normally be:

tblCandidate
CandidateID (PK)

tblCourse
CourseID (PK)

tblCandidateCourse
CandidateID ( composite )
CourseID (primary key )

tblCandidate stores values for the candidate (and ONLY the candidate) -
name, gender, date of birth etc.;

tblCourse stores values for the course (and ONLY the course) - title,
instructor, start date etc.;

tblCandidateCourse has two purposes. First, it indicates which candidates
are on what courses. Second, it stores values for that specific candiaet on
that specific course; perhaps, enrollment date.

IMO, CandidateID should >not< be in the course table. This violates the
principle that the course table is for values that pertain to the course AND
ONLY the course. This is things like course title; not things like candidate
ID or IDs.

HTH,
TC

Hi Robert and thanks,

I've cleared all data from the tables and also removed all indexes from both
the Primary and Related Tables apart from the Primary Key. This has not
changed anything.

I know it may seem unusual to have 5 attributes as a Primary key, so I'll
explain the logic. The database is for tracking learners reviews and
progress. This is an existing db. I have been asked to include various
other functionality such as tracking the NVQ's Key Skill and Further
Education for the learners.
Primary Key of Tables
================
tblCandidate => PK = CandidateID
tblCourse => PK = CandidateID+QualificationID+Scheme
tblFE => PK= CandidateID+QualificationID+Scheme+CourseTitle
tblFEModule => PK =
CandidateID+QualificationID+Scheme+CourseTitle+ModuleTitle
tblFEModuleExam => PK =
CandidateID+QualificationID+Scheme+CourseTitle+ModuleTitle+ResitNo

Each Candidate can undertake many courses. For each course a candidate
takes they may undertake 0 to many FE courses. For Each course they take
there will be 1 or more modules to that course. For each module they may
take the exam a number of times. The problem I am having is in creating the
relation ship between tblFEModule and tblFEModuleExam. I can create the
relationship, however when I try to enforce referential integrity I get the
error message

Tony

=== END SNIPPED TEXT ===