(SQL Server database) I have a table, User, with primary key on an int identity field, UserID. Another table, UserCDV, has as its PK the fields UserID and MessageID. UserCDV.UserID has an FK constraint from User.UserID.

UserCDV also has a field, OwnerID, which also has an FK constraint from User.UserID. UserCDV.UserID and UserCDV.OwnerID may not reference the same person, but they are both listed in the User table.

I need to be able to establish a relation from UserCDV to the User table with either UserID or OwnerID. However, when I generate the classes, UserCDVEntity.Relations.UserEntityUsingUserID is present; UserCDVEntity.Relations.UserEntityUsingOwnerID is not.

What can I do to cause the OwnerID relation to show up in the UserCDVEntity class's Relations collection?

Could you check in the application output window if there is some error logged like 'foreign key rejected... ' when you refresh the catalog? The code rejects foreign keys if they're defined on the PK table of the relation, which is perhaps the case (SqlServer allows that)

No. There are 16 error messages involving stored procedures with invalid objects. Only one of these is used in my Gen Pro project, and I wrote about it in a different thread below. There's a report of a table with no primary key; this is by design.

When I create 2 tables, User and UserCdv, user with the PK UserID, UserCdv with the PK UserID, MessageID and add an extra field OwnerID to UserCdv, and add an FK from UserCdv.UserID -> User.UserID and an FK from UserCdv.OwnerID -> User.UserID, I get both relations showing up in the designer. (as 1:n / m:1 relations)

When I generate the code (2 class scenario, selfservicing, but the others should be the same) I get both relations showing up. This is with the latest installer of 1.0.2004.1. (Feb 4th) (not the beta). Could you please check which version you're using?

I'm using version 1.0.2004.1 Final, released September 24th, 2004.

Maybe some images will help shed some light. Here are a diagram of the tables, the two relations dialogs, and the intellisense drop-down showing the available options after "UserCDVEntity.Relations".

http://pages.sbcglobal.net/fredsaw/images/GenPro/

UserCDV is an alias for the table UserClinicalDocumentViewer, obviously.

UserCDV originally had OwnerID as a nullable field with no relation to User. I made it non-nullable at the same time I created the FK on it. I did this after the Gen Pro project had been created, which was why it was necessary to refresh the catalog and re-generate. Could there be a problem with the refresh, maybe?

Okay, added another screen capture at the link above, this one showing the project explorer with User and UserCDV relations expanded. The relations are there.

Thanks for the screens. I see one of them is a custom relation, (the blue dot shows that). I'll try to do that too here tomorrow so I hope to have more news when the sun rises in texas

Okay, now, before I created the FK on OwnerID, while it was still a plain old nullable field, I did create a custom relation to establish the relationship. But it didn't show up in the regenned class, and in trying to figure out why not, I decided maybe the field being nullable was the reason. That's when I posted my first message here, asking how to delete the custom relation (the "embarrassingly obvious" question).

So I deleted the custom relation on both sides, made the changes to the tables, refreshed the catalog, and regenned.

I don't understand why there would be a custom relation when I deleted it. Shouldn't it be an intrinsic relation, based on the foreign key constraint?

Thanks for your help with this. Much appreciated.

Fred wrote:

Okay, now, before I created the FK on OwnerID, while it was still a plain old nullable field, I did create a custom relation to establish the relationship. But it didn't show up in the regenned class, and in trying to figure out why not, I decided maybe the field being nullable was the reason. That's when I posted my first message here, asking how to delete the custom relation (the "embarrassingly obvious" question).

So I deleted the custom relation on both sides, made the changes to the tables, refreshed the catalog, and regenned.

I don't understand why there would be a custom relation when I deleted it. Shouldn't it be an intrinsic relation, based on the foreign key constraint?

Thanks for your help with this. Much appreciated.

The screenshot you posted, GenProProjExp.jpg, shows that the relation UserCDV.OwnerID - User.UserID is still a custom relation.

Nevertheless, the custom relation also has to show up in the code. I'll try to reproduce the steps you took here and see if I can get your situation reproduced.

(edit) I can't reproduce it. I've removed the FK, refreshed the catalog, relation was gone, then I added a custom relation, generated code, worked fine. Then I added the FK and refreshed, the custom relation stayed (because it was already there, so it isn't overwritten by an FK based relation). I removed the custom relation, refreshed, FK based relation turned up, generated code, worked fine. I tried to use a table setup the same as you had, with a nullable field for OwnerID (which shouldn't and didn't make a difference).

I'd suggest to you to download teh latest version from the upgrades section and to check if that version generates correct code (that's the version I tested it with).

Well, I removed the custom relation, refreshed catalog and regenned, and suddenly the relation was there. Don't know what was wrong before, but at last I can get on with my programming. Yeah!

glad you can proceed.