Chapter 11 Page 378

GeeTee991100 · December 8th, 2010, 10:24 AM

Hi,

Compliments on an excellent guide.

I have another issue. I follow the steps on page 378 of the guide using Web Developer 2008 and SQL server 2008 express and it creates a one to one relationship between the Genre and review tables. How do I change it to a one to many relationship?

EDIT: Sorted it. I have to not select Review ID as a primary key. Surely if we start to use high volumes of data that will slow the database down?

Imar · December 8th, 2010, 10:36 AM

Quote:

EDIT: Sorted it. I have to not select Review ID as a primary key. Surely if we start to use high volumes of data that will slow the database down?

Hi there,

You definitely want to keep Review.Id as the primary key of the Review table so the problem must be elsewhere.

Maybe you were trying to join Genre.Id on Review.Id rather than on Review.GenreId?

Imar

GeeTee991100 · December 8th, 2010, 10:42 AM

Definitely not got the order wrong.

If I select the Review ID as a primary key it reverts back to a one to one regardless of which order I place the table. I've played around with it, changed the order and tried quite a few things and it always creates a one to one relationship but as soon as I deselect Review ID as a primary key it reverts to a one to many. Could it be a SQL 2008 express issue?

EDIT: Just tried creating the same diagram in SSMS 2008 express and I get the same issue. Do you know anyway of getting around this? Thanks.

GeeTee991100 · December 8th, 2010, 11:17 AM

HI,

I am still having the same issue. Do you know of any forum I can look at that might have a solution?

Thnaks

Imar · December 8th, 2010, 11:21 AM

Quote:

but as soon as I deselect Review ID as a primary key it reverts to a one to many.

To me that seems as if the database tries to join both primary keys (Genre.Id on Review.Id) instead of a primary key and a foreign key (Genre.Id and Review.GenreId).

Quote:

Definitely not got the order wrong

Not sure what you mean with that. What "order" are you talking about?

Quote:

Could it be a SQL 2008 express issue?

I don't think so; the entire book was written and tested against SQL Server Express. For this particular feature, there shouldn't be a difference between 2005 and 2008.

When you drag Genre.Id to Review.GenreId, two dialog boxes pop up as explained in the book. What do you see in the top most window? That is, what does it list as Primary key table and Foreign key table, and which columns does it list for these tables?

Imar

Imar · December 8th, 2010, 11:22 AM

Quote:

Do you know of any forum I can look at that might have a solution?

You're in the right forum already. Just tell me what you do and see and I can help you make this work.

Cheers,

Imar

GeeTee991100 · December 8th, 2010, 11:33 AM

Sorry, by order I thought you meant I was trying to join review first to genre second. With Review as the primary key table table and Genre foreign key. I was pointing out that no matter which way I did it I was still getting the same result.

The pop up is as follows.

http://i156.photobucket.com/albums/t...T_02/diag1.jpg

GeeTee991100 · December 8th, 2010, 11:36 AM

Ooops I see it now. I was joining id to id. Sorry for the mistake. Thanks for the help

Imar · December 8th, 2010, 11:52 AM

Quote:

Ooops I see it now. I was joining id to id.

Exactly what I thought. Glad it's working now.

Quote:

by order I thought you meant I was trying to join review first to genre second.

FYI, it shouldn't matter in what order you do this. SQL Server should be able to figure out the relationship with both options.

Cheers,

Imar