Foreign key onto weak entity set in MS SQL Server 2008?
- by Nic Waller
I'm using Microsoft SQL Server 2008 Management Studio to create a relational schema by following an Entity-Relationship diagram. (included below; unrelated details removed)
Until now, primary and foreign keys have been working as expected. But when I try to create a foreign key from the entity relationship takes onto the weak entity set section, I get an error. Section is identified by a composite primary key in the context of course, which has course_id as a primary key. Therefore, the primary key of section is a 4-way composite key.
The entity relationship takes needs to refer to section, so it includes all 4 primary attributes from section in it's own primary key. When trying to establish a foreign key relationship from takes to section, Studio gives the following error:
The columns in table 'section' do not match an existing primary key or UNIQUE constraint.
Am I doing something wrong, or is this an unsupported configuration? I can provide more details or the SQL schema if necessary.
