| Author |
Topic |
|
russray
Starting Member
27 Posts |
 Posted - 2008-03-08 : 12:17:51
|
Okay, I've tried to follow MSND instructions on creating a many-to-many relationships using the SSMS. I can create the two primary tables and create the PK for each. I can create a cross-reference table with two PKs. Now I am trying to create the one-to-many relationships for each primary tables and I can't seem to get it right. SSMS complains iff I attempt to make the relationships on either side (primary tables or xref table)I used these steps from MS.http://msdn2.microsoft.com/en-us/library/aa176955(SQL.80).aspx When I do a seach on this, I get no new links for SQL Server 2005.Can someone throw me a link?Thank you!Russ |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 12:20:57
|
What is the part you are stuck on?Did you create the Foreign Key relationship or constraing also ?http://msdn2.microsoft.com/en-us/library/aa937370(SQL.80).aspx
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
russray
Starting Member
27 Posts |
Posted - 2008-03-08 : 12:30:30
|
| Thank you for taking some time to come to my aid.What I have created is the following: 1. Table A with primary key of a_id numeric(18,0). This is marked as an identity. 2. Table B with primary key of b_id numeric(18,0). This is marked as identity. 3. Table A_B_xref. The table has two primary keys:xref_a_id and xref_b_id. The instructions for calling for creating a one-to-many relations back to the each table-- A and B. Obviously, I don't have something setup correctly.....SSMS complains when I try to make a relationship.Russ |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 12:42:50
|
So you need to set up your relationshipA_B_xref needs to have all of the combined PK's from the A and B and BOTH columns in the x_ref table would be a composite primary key.Your relationship diagram should be set up that the xRef table is on the MANY side of two separate 1:many (one from A to xref and one from B to xref)Such that A : A_B_xref is 1:manyB : A_B xref is 1:manyI am not completely certain,but I think you also set up Foreign keys in the xref table for each column and it's referenced column in A, or B respectively...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
russray
Starting Member
27 Posts |
Posted - 2008-03-08 : 14:21:51
|
quote:
Originally posted by dataguru1971
So you need to set up your relationshipA_B_xref needs to have all of the combined PK's from the A and B and BOTH columns in the x_ref table would be a composite primary key.>>>>> I have this. :-)Your relationship diagram should be set up that the xRef table is on the MANY side of two separate 1:many (one from A to xref and one from B to xref)>>>>I understand this. This is where I am trying to create the relationship, but SSMS is complaining..... The reason is because I can not determine which fields I should make the reference to. A can not reference Composite PK because the field numbers do not match up.Such that A : A_B_xref is 1:many>>>>> This really means in XREF table a FK exist to AB : A_B xref is 1:many>>>>> This really means in XREF table a FK exist to BI am not completely certain,but I think you also set up Foreign keys in the xref table for each column and it's referenced column in A, or B respectively...
Poor planning on your part does not constitute an emergency on my part.
Did I get that right? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 14:35:27
|
Hold on, let me run DBCC OPENREMOTEVIEWER ('WORMHOLE') and look. ;)According to this quote of yours:quote:
3. Table A_B_xref. The table has two primary keys:xref_a_id and xref_b_id.
You did not.That table should have one primary key, comprised of BOTH columns as a composite. That is my understanding of the instructions at the link you posted anyway.I "think", but am not sure, that each of those two x-ref columns should also have a FK (one each) referencing to the appropriate column in the table which that column is the primary key. maybe this http://www.profsr.com/sql/sqless02.htm explains it a little more clearly than the msdn I suppose
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
russray
Starting Member
27 Posts |
Posted - 2008-03-08 : 14:58:46
|
quote:
Originally posted by dataguru1971
Hold on, let me run DBCC OPENREMOTEVIEWER ('WORMHOLE') and look. ;)According to this quote of yours:quote:
3. Table A_B_xref. The table has two primary keys:xref_a_id and xref_b_id.
You did not.>>>>>>Sorry, I misspoke/typed. It has two fields when combined makeup the PK. This the composite you spoke of..... :-)That table should have one primary key, comprised of BOTH columns as a composite. That is my understanding of the instructions at the link you posted anyway.I "think", but am not sure, that each of those two x-ref columns should also have a FK (one each) referencing to the appropriate column in the table which that column is the primary key. >>>>> I am going to try this. maybe this http://www.profsr.com/sql/sqless02.htm explains it a little more clearly than the msdn I suppose
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 15:25:00
|
Ahh....I see..you put your responses INSIDE my quote...LOL...good luck. I am not certain, b/c I have not set up a many:many DB. I was merely reading the same information to see if I interpreted differently than you were.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
russray
Starting Member
27 Posts |
Posted - 2008-03-08 : 15:29:33
|
| okay... that work..THANKS SO MUCH FOR YOUR HELP!!!!!! |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 15:33:31
|
quote:
Originally posted by russray
okay... that work..THANKS SO MUCH FOR YOUR HELP!!!!!!
Was it the lack of a composite primary key on the "junction" table?or did you have to add FK's from that "junction" for referencing the two other tables?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
Creating Many-to-Many Relationship