This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I am having trouble with creating many-to-many relationships in the designer for tables that already exist and which I cannot modify. I have created several such relationships for new tables, but the existing tables are causing me trouble and I need some help. I have been using the documentation here to guide me: I am using the MySQL .NET Connector's MySqlMembershipProvider in my application. This provider creates its own tables, some of which I would like to include in my LightSpeed models. They have a User table and a Role table, each of which has an Int32 PK field named ID. The M-to-M intersection table has a UserID and a RoleID in each row, but no PK for the table, which may or may not be why LightSpeed is upset. I tried creating the M-to-M relationship between the User and Role tables, explicitly telling it to use the UserRole table as the intersection, but that didn't work. I got the following errors: I then deleted the M-to-M relationship and created the 1-M relationships myself. That resulted in the error: Can anyone see what I am doing wrong, here? Can I "link" these pre-existing tables together and to my new tables using the LightSpeed designer? Thank you! Tony |
|
|
|
|
Hi Tony, Have a look at http://www.mindscapehq.com/blog/index.php/2010/06/17/many-to-many-associations-and-composite-keys-in-lightspeed/ and see if that helps. What's basically happening is that LightSpeed automatically generates a RoleId field for the Role association, and that's clashing with the explicit RoleId field (which may be under Properties or may be part of the ID). Setting the Key Property Reference tells LightSpeed, "Use this field over here as the FK instead of generating your own," which will make the clash go away. The slight catch in your case is that you say the intersection table in the database doesn't have a primary key. That's fine, but LightSpeed does require an ID. Normally that would be an ID column, but since the database doesn't have one of those, you'll need to use a composite key of RoleId + UserId as your LightSpeed ID. Normally the designer would infer the composite key for you, but if the database doesn't define a PK, it won't infer the composite key (which means it will fall back on assuming an Id column instead, which isn't going to work!). So you may need to define the composite key manually, which means going into the LightSpeed Model Explorer (View > Other Windows > LightSpeed Model) and adding fields to the Identity Properties collection by hand. Hope this makes sense -- let us know if you need any further info. |
|
|
|
