Optional OneToOne not honoured on SQL Server 2005

[ejb3workshop]

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.

[ejb3workshop]

The same occurs using the JTDS Driver (jtds-1.2-dist.zip).

[ejb3workshop]

I found this article on the web, which shed some light on the problem. Seems like a bug (aka feature) of SQL Server.

http://www.sqlmag.com/Article/ArticleID/25259/sql_server_25259.html

and

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48614

[ejb3workshop]

This might also be of interest

http://groups.google.co.uk/group/microsoft.public.sqlserver.server/browse_thread/thread/e4a0d0f81f4ef42/7d9c3c9461435c9e?lnk=st&q=%22SQL+Server%22+Unique+Key+constraint+NULL&rnum=5&hl=en#7d9c3c9461435c9e

It seesm pretty clear to be isolated to SQL Server, so not strickly speaking related to this forum. Yet in the context of EJB3 this poses a (significant) limitation. The freedom achieved with EJB3 entities is restricted and this is a problem which should be relevant to this forum.

Alex
ejb3workshop.com