Defining Multiple Foreign Keys in Visual Studio

Question

I have a basic database schema as follows (dumbed down so it's easy to read):

[Staff]
StaffId (pk)
FirstName
LastName

[RosterEvent]
EventId (pk)
StartDate
EndDate

[StaffEvents]
StaffId (pk)
EventId (pk)

Ok so, many Staff can have many RosterEvents, which is why I added the StaffEvents table. It resolves the many-to-many relationship. However I do not know how to define the foreign keys.

In Visual Studio, how do I define these as foreign keys using the Table Designer? If I try to add the relationship using the Foreign Key Relationships dialog box, I get the error message "The columns in table 'StaffEvents' do not match an existing primary key or UNIQUE constraint". Even though the UNIQUE constraint has been applied to all primary keys on every table.

Help is much appreciated! Thanks!

Answer 1

In the table designer you can click the row headers on the side of the two columns you want and click the primary key icon on the toolbar.

Are your PK actually marked as Primary Keys or just UNIQUE? Are they the same data-type?

Answer 2

All primary keys on every table are marked as PK and UNIQUE. They are also all of type int.

This is how I am trying to set up the relationship at the moment.

1. From the Staff table I click the 'Relationships' toolbar button.
2. I click 'Add'
3. I click on the (...) from the 'Tables and Columns Specifications' property.
4. I select 'StaffEvents' as the primary key table.
5. I select 'StaffId' from the drop down box.

When I click OK, I get the error message "The columns in table 'StaffEvents' do not match an existing primary key or UNIQUE constraint".