0

I am building a system where there are properties and prospects, I have a table for both, but as I see this as a many to many relationship, I have created a table called prospect_property with two columns prospect_id and property_id, these are both/composite primary key(s).

What I am having problems with is setting the foreign keys and binding it all together. I figure the composite key should be the foreign key on the other two tables (or is it the other way around), but when trying to create that relationship in Management Studio, I have to reference one to one field, but I have one vs. two...

How am I supposed to set the relationship between these tables so that I for a prospect can see all properties and for a property can see all prospects?

SQL SERVER 2012 (Express)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jesper
  • 999
  • 3
  • 14
  • 30
  • why don't you add another column to be the primary key for your table and then set an composite index on this two fields? – Maryam Arshi Nov 20 '12 at 09:20

1 Answers1

3

1 Property --> several prospect_property (link on PropertyId on both sides)
1 Prospect --> several prospect_property (link on ProspectId on both sides)

Property: PropertyId is PK
Prospect: ProspectId is PK
prospect_property: composite PK made of PropertyId and ProspectId
PropertyId is FK to Properties and ProspectId is FK to Prospects

There is no more than this. The rest wil be done by buiding a few views linking all that togheter.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Which Fields on which tables should be set as primary/foreign keys then? – Jesper Nov 20 '12 at 09:34
  • Thanks for helping. But when trying to set this up, I have an issue. I have set PROSPECT.PROSPECTID and PROPERTY.PROPERTYID as primary keys and PROSPECT_PROPERTY.PROSPECTID/PROSPECT_PROPERTY.PROPERTYID as composite primary key. Now when I try to set the relationship, I open PROPERTY -> Right click on PROPERTYID -> Relationships, then in the window, select Primary Key Table=PROSPECT_PROPERTY and foreign key table=PROPERTY and columns PROPERTYID for both of them, but I get the error "The columns in table 'PROSPECT_PROPERTY' do not match an existing primary key or UNIQUE constraint'. – Jesper Nov 20 '12 at 20:23
  • OK, I found out that I should make the foreign key relationship on the PROSPECT_PROPERTY table's columns in Management Studio and just set the second primary key column to none. So it's all good now. THANKS – Jesper Nov 20 '12 at 21:30