3 Replies Latest reply on Apr 28, 2006 5:49 AM by Alexander Hartner

    Optional OneToOne not honoured on SQL Server 2005

    Alexander Hartner Expert

      We recently switched from PostgreSQL 8.1 to SQL Server Express 2005 (Please don't ask why). Within the EJB3 application there is a optional OneToOne relationship configured as follows :

      @OneToOne(cascade=CascadeType.ALL, fetch = FetchType.LAZY)
      @JoinColumn( name="smartlistid")
      public SmartList getSmartList()
      {
       return smartList;
      }
      
      public void setSmartList(SmartList smartList)
      {
       this.smartList = smartList;
      }
      


      While we where using Postgres this worked fine, but since we moved to SQL Server we are havning problem. Hibernate (presumably) has greated a unique constraint on the table to ensure that each object only has one smartlist linked. Yet it doesn't seem to be able to ignore NULL values when it performs the constraint validation. In Postgresql NULL's are ignored and I can have many objects without corresponding SmartLists. Yet on SQL Server this causes a constrain violation, as it checks that each "value" is unique without ignoring the NULLs.

      I have tried this with the M$ JDBC driver (sqljdbc_1.0.809.102_enu.exe).

      Any suggestions other then to switch back to Postgres would be great.