Possible Primary Key Problem

I am struggling to get a relationship to work in my database and I cannot
get my head round the problem so I wondered if any of you knowledgeable
people on here knew the answer.
An image of my Diagram is here
(http://www.step-online.org.uk/relationship.png) showing some of the
relationships.
Everything works find as is.
I am now trying to create a many-to-many-to-many type of relationship (if
this is even possible).
My courses tables work fine as a many-to-many relationship.
I have created a new join table so I can link individuals to courses (using
the unique ID column in the Courses join table).
All the fields ending _ID are nnumeric data type with an identity set to
increment by 1 each time.
I have tried to create a relationship between XREF_Courses_ID in the
SYS_Xref_Join_Ind_Courses table and the same field (both primary keys) in
the SYS_Xref_Join_Courses table, and receive the following error:
"The columns in table 'SYS_Xref_Join_Courses' do not match an existing
primary key or UNIQUE constraint".
Why would this be?On Tue, 6 Apr 2004 12:22:53 +0100, "Keith" <@..> wrote:

>I am struggling to get a relationship to work in my database and I cannot
>get my head round the problem so I wondered if any of you knowledgeable
>people on here knew the answer.
>An image of my Diagram is here
>(http://www.step-online.org.uk/relationship.png) showing some of the
>relationships.
>Everything works find as is.
>I am now trying to create a many-to-many-to-many type of relationship (if
>this is even possible).
>My courses tables work fine as a many-to-many relationship.
>I have created a new join table so I can link individuals to courses (using
>the unique ID column in the Courses join table).
>All the fields ending _ID are nnumeric data type with an identity set to
>increment by 1 each time.
Aaarrgghhhhhh...

>I have tried to create a relationship between XREF_Courses_ID in the
>SYS_Xref_Join_Ind_Courses table and the same field (both primary keys) in
>the SYS_Xref_Join_Courses table, and receive the following error:
>"The columns in table 'SYS_Xref_Join_Courses' do not match an existing
>primary key or UNIQUE constraint".
>Why would this be?
>
Because the columns in that table do indeed not match an existing
primary key. Both tables hava a primary key over multiple columns, so
you can't use just one of these columns to create a relationship.
Explanation: any foreign key (which is what a relationship translates
to) needs to join on at least one side to a column or column
combination that identifies at most one row. Generally the primary
key; optionally a column combination with UNIQUE constraint (a
candidate key).
A many-to-many relationship can only be implemented through an extra
table. For the relationship between courses and individuals, something
like:
CREATE TABLE CourseSubscriptions
(IND_ID int not null,
COURSE_ID int not null,
PRIMARY KEY(IND_ID, COURSE_ID),
FOREIGN KEY(INT_ID)
references SYS_Individual(INT_ID),
FOREIGN KEY(COURSE_ID)
references SYS_Courses(COURSE_ID))
On second thoughts - it seems as if the Xref-Join-Ind-Courses (who
comes up with such names?) is already made for this purpose. Just get
rid of the superfluous XREF_Ind_Course_ID and create the one missing
foreign key constraint.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)