sql server - NHibernate incorrectly creating ManyToMany table - Cannot define PRIMARY KEY constraint on nullable column error -

i have started implementing iset's instead of ilist's in project better reflects collection's have.

this has thrown odd error in creation of 1 table. error i'm getting is.

cannot define primary key constraint on nullable column in table 'useraddressassignments'.

i have many many relationship between users , addresses.

public class user : baseentity {     public virtual system.guid userguid { get; set; }     public virtual string username { get; set; }     public virtual company company { get; set; }     public virtual iesi.collections.generic.iset<address> useraddressassignments { get; set; } } 

the sql generating lookup column causing issue , shown below. userid column being nullable causing problem.

create table useraddressassignments (     useraddressassignmentid int not null,    addressid int identity not null,    userid int null,    primary key (userid, addressid) ) 

the odd part here have identical relationship between company , addresses works fine.

public class company : baseentity {     public virtual system.guid companyguid { get; set; }     public virtual string name { get; set; }     public virtual iesi.collections.generic.iset<address> companyaddressassignments { get; set; } } 

this create correct sql lookup table.

create table [dbo].[companyaddressassignments](     [companyid] [int] not null,     [addressid] [int] not null, primary key clustered  (     [companyid] asc,     [addressid] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary] 

with clustered key correct not null attributes keys , no third 'companyaddressassignmentid' column users lookup column.

the mapping overrides both company , user identical too.

public class companymappingoverride : iautomappingoverride<company> {         public void override(fluentnhibernate.automapping.automapping<company> mapping)         {             mapping.map(x => x.name);             mapping.hasmanytomany(x => x.companyaddressassignments).table("companyaddressassignments").cascade.all();         } }  public class usermappingoverride : iautomappingoverride<user> {     public void override(fluentnhibernate.automapping.automapping<user> mapping)     {         mapping.references(x => x.company).cascade.all();         mapping.hasmanytomany(x => x.useraddressassignments).table("useraddressassignments").cascade.all();     } } 

does have suggestions on why 2 lookup tables different when far can tell structure , mappings of classes identical?

i have resorted using ilist user addresses work around issue.

this issues create sql.

create table [dbo].[companyaddressassignments](     [companyid] [int] not null,     [addressid] [int] not null, primary key clustered  (     [companyid] asc,     [addressid] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary] 

any ideas on causing discrepancy?

nhibernate version 3.2.0.4000.

cheers

steve

right i've found out issue was.

the site i'm working on has had nhibernate added it. in order use code first / schema generation approach used automated tool convert existing schema classes schema magically created from.

i used nhhibernate designer auto-generated classes , cleaned entities match our standards.

http://www.devart.com/entitydeveloper/nhibernate-designer.html

the error crept in tool generated class lookup table. useraddressassignment has own class in system. when creating many many relationship not required. having specified both class many many , in mapping override given name of manytomany table nhibernate must have got confused (unsurprisingly).

i point out schema nhibernate designer had work not clean of relationships not enforced, not blaming creating entity when not required!

so when removed unnecessary useraddressassignment class given below worked expected.

  public class useraddressassignment : baseentity     {         public virtual int addressid { get; set; }         public virtual user user { get; set; }         public virtual address address { get; set; }     } 

hope helps one!

cheers

steve