>I don't think what you want is possible because it goes against concepts >of row indentity and referential integrity.
Sorry, but that is incorrect. There is no restriction on having more than one key that can uniquely identify a row. All of the keys which can serve to identify a row are called candidate keys. From the candidate keys, one of the keys is chosen as the primary key; the remaining keys are then referred to as alternate keys. > A foreign key in one table implies a relation to the primary key in the > other table, and there can be only one primary key. > > The normal way to specify the relationship you want is to add a user id > attribute to the UserRole class and use the inverse-foreignkey attribute > in the user roles' collection descriptor. Over lunch, I had a discussion with a colleague about this. One issue that came up during the discussion was that if your alternate key can change, then it should not be used as the foreign key part of a composite primary key in a related table because primary keys should never change. So, by virtue that you would have to cascade the alternate key changes to the primary key of a related table, then the alternate key is no longer acceptable as a foreign key in the related table. One other solution that came up was to have the following tables... User (userId, username, password) UserRole (userId, rolename) ...and the following view... UserRoleView (username, rolename) ...based on the User/UserRole relationship. This approach nicely achieves my goal of preserving normalization (not duplicating both userId and username in the UserRole table), and still providing the data navigation required by DataSourceRealm/JDBCRealm to find roles based on username/password credentials. So, I get to have my cake (UserRoles collection in the User class based on userId relationship to UserRole) and eat it too (still compatible with Tomcat Realms). "Pulat Yunusov" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >I don't think what you want is possible because it goes against concepts >of row indentity and referential integrity. > > A foreign key in one table implies a relation to the primary key in the > other table, and there can be only one primary key. > > The normal way to specify the relationship you want is to add a user id > attribute to the UserRole class and use the inverse-foreignkey attribute > in the user roles' collection descriptor. > > Pulat > > Sean Dockery wrote: >> Hello. >> >> I've searched the gmane archives, but I was unable to find any messages >> related to my question. >> >> Suppose that I have the following classes... >> >> public class User { >> private Integer id; // primary key field >> private String username; // alternate key field >> private Collection userRoles; // collection of UserRole objects >> related by username >> }; >> >> public class UserRole { >> private String username; // foreign key >> private String rolename; >> } >> >> ...and I wanted to relate User and UserRole in a repository mapping >> based on the equality of the username fields in both User and UserRole. >> Can this exact schema be handled through a repository mapping? (Does >> fk-pointing-to-this-class work without using an indirection-table? Is >> there an alternative?) >> >> Most of the rest of the data model in the application uses User.id as a >> foreign key, so we can't easily change the primary key of the user >> table. >> The UserRole fields were chosen for compatibility with Tomcat >> JDBCRealm-like declarative security implementations. >> The only way that I can see to work around this problem is to duplicate >> the User.id field in the UserRole table, along with the username. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
