When creating two tables with one referencing another the 3rd table (lookup table) has reversed foreign keys.

I.E. I have two tables: Store and Employee. Both an employee and store get a unique guid. The table that gets generated as a look up Store_Employees has two columns (store's guid and employee guid). The foreign keys on these fields are reversed. So that the store guid is a foreign key to the employee's table guid and vice-versa.

Let me know if you need more details to replicate this.

Hi Yes, thanks for the feedback, I'd love to know more details and I'll fix it.



 

The simplest repo I can come up with is to create two tables called Store and Employee. Have Store contain an element of type 'employe[]' and have the employee table have an element reference the store. (i.e. a store has many employees and an employee is employed at one store).

The sql tables generated by this will be store, employee, and store_employees. The store_employees tables contains two columns thus linking an employee to a store. The foreign key references generated by gennit have them reversed. Specifically:

CREATE TABLE [Store_employees] (
    [StoreID] [uniqueidentifier] NOT NULL,
    [EmployeeID] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Store_employees] PRIMARY KEY CLUSTERED (
        [StoreID],   
        [EmployeeID]
    ) ON [PRIMARY],
   
    CONSTRAINT [FK_Store_Employee] FOREIGN KEY([StoreID])       -- WRONG
    REFERENCES [Employee] ([EmployeeID]),                      --WRONG, should be REFERENCES [Store] ([StoreID])        

    CONSTRAINT [FK_Employee_Store] FOREIGN KEY([EmployeeID])  
    REFERENCES [Store] ([StoreID])                                   -- WRONG, should be REFERENCES [Employee] ([EmployeeID])
     
) ON [PRIMARY]   
GO

I think basically the keys are accidently reversed.

-Daniel
 

Oh and this is with the NHibernate 1.2 generator...forgot to mention!

   CONSTRAINT [FK_Store_Employee] FOREIGN KEY([StoreID]) 
    REFERENCES [Store] ([StoreID]),                     

    CONSTRAINT [FK_Employee_Store] FOREIGN KEY([EmployeeID])  
    REFERENCES [Employee] ([EmployeeID])

Would the above be correct? What is your login daniel i'll extend your one month to 12 for your feedback.                  
 

All my test data has worked fine with it wrong which is a bit strange

it's pactuul@gmail.com

Sorry about the late reply as I've been swamped with coding projects.

On a second look at it, once I started using the gennit code in a project I noticed what you noticed: that the code works as intended, if you swap the guid's in each column. The xml mappings seem to be fine. So its quite possible that I've misunderstood how to use the tables and/or code.

So a quick recap. With no modifications to the generated code and sql tables it appears that the foreign keys are needing to be swapped as we discussed. I.E. Creating a store in the Store Table and an employee in the employee table requires that I put the employee's guid in the storeid column in Store_Employee and the store's guid in the employeeid column..

If I do this  the code works fine and nhibernate's mapping seems to be working properly with the relationships on my select statements.
The nhibernate mapping in store.hbm.xml looks correct (key column = employeeid is correct). As in:

<set access="property" lazy="true" name="Employees" table="Store_employees">
      <key column="EmployeeID" />
      <many-to-many column="StoreID" class="Core.Domain.DataAccess.Employee, Core" />
    </set>

It throws me off though that the guids in the store_employee table are needing to be "swapped".

  
 

Sorry for my delayed response. I'm busy writing some non-C# templates at the moment but thanks for the feedback definetly something I will work on. Your pro account has been extended to November 2008.