INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Creating two relations to the same datatable field

Creating two relations to the same datatable field

(OP)
Hello,

I'm working in the dataset designer in Visual Studios 2012 and I would like to create two relations between two tables. The one table is the "Courses" table, and the other is the "CourseRelation" table. Basically, the CourseRelation table relates one course to another. So I want to create two relations between the two tables. For the first relation, I want to relate CourseRelation.CourseID to Courses.ID, and in the second relation, I want to relate CourseRelation.RelatedCourseID to Course.ID.

But when I do this in the Relation dialog, it says:

"Cannot create a Key when the same column is listed more than once: 'ID'

These kinds of relation aren't impossible and I can't be the first one to try it. How can I accomplish what I want to accomplish?

RE: Creating two relations to the same datatable field

Are you perhaps trying to create the relations in the wrong direction?
Your CourseRelation Table has the foreign keys (N side of a 1:N relation), the Course.ID is the primary key (1 side of a 1:N relation) of the two relations.
I always forget what field needs to be dragged to which, but after doing one relation simply inspect its properties to see what is 1 and N side and if that is really correct.
If you mean the right thing but unintendedly do the wrong thing, you'll make a primary key a foreign key into two different tables, which would be weird to say the least.

Bye, Olaf.

RE: Creating two relations to the same datatable field

I see, but that's wrong. You're tying to do one relation named Courses_CourseRelation1 to CourseID and RelatedCourseID.
You need two relations, one from Course.ID to CourseRelation.CourseID and one from Course.ID to CourseRelation.RelatedCourseID

You can't crunsh that into one relation. That's all there is to it.



Bye, Olaf.

RE: Creating two relations to the same datatable field

(OP)
Oh, I see.

You're right. I was able to create two relations by changing the name of the first one and then linking the other FK to Course.ID.

Thanks for the help.

RE: Creating two relations to the same datatable field

Just FYI: The dialog allows adding more than one field, as you are not forced to make a single primary key field and therefore that and the foreign key might be composed from several fields. But the dialog is about one relation, it doesn't list all relations of the two tables.

Bye, Olaf.

RE: Creating two relations to the same datatable field

One more thought.

I don't know if this will apply here, but if the relation should be symmetric, eg two courses relate to each other, then you need two records for the two ID combinations.
The way you have it it's asymetric as in Facebook friendships not confirmed by the one or other. Eg Course 2 may be related to Course 1, but if the mirrored record doesn't exist Course 1 isn't related to Course 2.

You may only store one relation with CourseID<RelatedCourseID and use it for both relation directions, but in queries that'll mean two joins looking up a Course.ID as CourseID or RelatedCourseID.

If it should be like family relations, where there is no one way relationship (no matter if you like it or not) I'd design a RelatedCourses table with a family or group number and CourseID. All related courses get the same group number here. If three courses relate to each other then you add their IDs in RelatedCourses with the same group number. Groups may overlap.

For example

GroupNo CourseID
----------------
      1        1
      1        2
      2        1
      2        3
      2        4 

Will mean Course 1 is related to course 2 via GroupNo 1 and to Course 3 and 4 via GroupNo 2.

In your table you'd need
CourseID RelatedCourseID
------------------------
       1               2
       2               1
       1               3
       3               1
       1               4 
       4               1
       3               4
       4               3 

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close