Fluent nHibernate: How to map 2 tables with no primary keys defined

Question

I have a legacy system where the relationship between 2 tables haven't been defined explictly and there aren't any keys (primary or unqiue defined). The only related columns is 'Username'

Something like:

Table: Customer Column: Id, Column: Username, Column: FirstName,

Table: Customer_NEW Column: Username Column: FirstNameNew

I have following Mapping definitions:

 public sealed class CustomerMap : ClassMap<Customer>, IMap
 {
    public CustomerMap()
    {
        WithTable("customers");
        Not.LazyLoad();
        Id(x => x.Id).GeneratedBy.Increment();
        References(x => x.CustomerNew, "Username")
            .WithForeignKey("Username")
            .Cascade.All();

 }

  public sealed class CustomerNewMap : ClassMap<CustomerNew>, IMap
  {
    public CustomerNewMap()
    {
        WithTable("customers_NEW");
        Not.LazyLoad();
        Id(x => x.Username).GeneratedBy.Assigned();
        Map(x => x.FirstNameNew);
    }
  }

The problem is nHibernate is generating an UPDATE statement for the 'Reference' in the Customer Mapping definition which is obviously failing when attempting to insert a new Customer object which has a reference to CustomerNew object.

How do I get the mapping definition to generate the INSERT statement instead of an UPDATE for the 'Save' command?

Cheers in advance

Ollie

Answer 1

The reason is because there isn't any referential integrity in the database, no primary keys, composite keys not foreign key constraints…

Yet again another application that has a database that’s not fit for purpose…