Problem with delete operation in many to many relation

Question

Hi,

I've got to classes Product and Store which have many to many relation

I want deleting of store not to cause deleting of related product And deleting of product not to cause deleting of related store.

Currently deleting of entity cause exception due to Foreign Key constraint.

Here is this classes and their mapping in fluent hibernate:

public class Product
{
    public Product()
    {
        this.StoresStockedIn = new List<Store>();
    }


    public virtual string Name { get; set; }

    public virtual double Price { get; set; }

    public virtual long ProductID { get; set; }

    public virtual IList<Store> StoresStockedIn { get; set; }

}

public class Store
{
    public Store()
    {
        this.Products = new List<Product>();
        this.Staff = new List<Employee>();

    }

    public virtual string Name { get; set; }
    public virtual IList<Product> Products { get; set; }
    public virtual IList<Employee> Staff { get; set; }
    public virtual long StoreID { get; set; }
}

public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        this.Id(x => x.ProductID);
        this.Map(x => x.Name);
        this.Map(x => x.Price);
        this.HasManyToMany(x => x.StoresStockedIn)
            .Cascade.None()
            .Table("StoreProduct");
    }
public class StoreMap : ClassMap<Store>
{
    public StoreMap()
    {
        this.Id(x => x.StoreID);
        this.Map(x => x.Name);
        this.HasManyToMany(x => x.Products)
            .Cascade.None()
            .Inverse()
            .Table("StoreProduct");
        this.HasMany(x => x.Staff)
            .Cascade.All()
            .Inverse();
    }
}

Thanks, Alexey Zakharov

Answer 1

Taking Hibernate out of the mix and just thinking from the database perspective, it appears that your product entity keeps its own list of stores it is stocked in. I recommend separating out that information so you have an association entitity called product store that would associate a product with different stores. Example:

Product(name, id) Store(id, name, address)

ProductStore(product_id, Store_id, quantity_in_store....).

This should address the kind of problem you describe.

Keep business rules in mind: If a store is deleted, I suspect the products in the store need to be accounted for and so on . . .

K

Answer 2

if you choose to set the Cascade parameter at NONE, it's up to you to manage this relation and so to put the store property value in the product to null before deleting the store. Like Karl said often you come with another class (association class) to hold additionnal information about the relation.