SQL Server 2008 Express - Got "The columns do not match an existing primary key or UNIQUE constraint" despite constraints being set

I have the following tables

ModuleClass ( <ModuleID>, <Section>, <Number>, StartDate, EndDate)
ClassEnrollment ( <ModuleID>, <Section>, <Term Code>, <User Name>, Role)

Now a ModuleClass entity can have more than one enrollment, so there is a 1 to M relationship. However, wh开发者_如何学JAVAen attempting to define FK between ModuleClass and ClassEnrollment using ModuleID and Section, I get

The columns in table ClassEnrollment do not match an existing primary key or UNIQUE constraint

However, both ModuleID and Section are participating in a PK constraint.

(I am using the Visual Database Tools to create the tables and specify the relationship).

What is a better way of representing this relationship?

It looks like the primary key of ModuleClass is a composite key consisting of three columns, (ModuleID, Section, Number). To set a foreign key reference to that table, you'll have to target all three of those columns.

To target all three of those columns, you'll need to include the column "Number" in the table ClassEnrollment. Then you can set

FOREIGN KEY (ModuleID, Section, Number) 
  REFERENCES ModuleClass (ModuleID, Section, Number)

Are you sure you didn't specify the FK relationship backwards? This is an easy mistake to make using GUI visual tools. You have to make sure that every row in ClassEnrollment references one row in ModuleClass.

If you specify the relationship in the reverse direction, you'll get exactly the error you reported.