@OneToOne mapping DDL generation for MS SQL Server

[Ryszard-Trojnacki]

In new ebean (4.5) there has beed added UNIQUE CONSTRAINT for One to One mapping.
Generated SQL by ebean 3.4.1 for MS SQL Server 2012:

create table users (
  id                        numeric(19) identity(1,1) not null,
  username                  varchar(48) not null,
  preferences_id            numeric(19),
  user_type                 integer,
  constraint ck_users_user_type check (user_type in (0,1,2,3,4,5)),
  constraint uq_users_username unique (username),
  constraint pk_users primary key (id))
;

create table users_preferences (
  id                        numeric(19) identity(1,1) not null,
  username                  varchar(48) not null,
  ui_preferences            varchar(max),
  user_credentials          varchar(max),
  constraint pk_users_preferences primary key (id))
;

ebean 4.5.4-SNAPSHOT for MS SQL Server 2012:

create table users (
  id                        numeric(19) identity(1,1) not null,
  username                  varchar(48) not null,
  preferences_id            numeric(19),
  user_type                 integer,
  constraint ck_users_user_type check (user_type in (0,1,2,3,4,5)),
  constraint uq_users_username unique (username),
  constraint uq_users_preferences_id unique (preferences_id),     # new UNIQUE CONSTRAINT
  constraint pk_users primary key (id))
;

create table users_preferences (
  id                        numeric(19) identity(1,1) not null,
  ui_preferences            varchar(max),
  user_credentials          varchar(max),
  constraint pk_users_preferences primary key (id))
;

In new ebean there is:

  constraint uq_users_preferences_id unique (preferences_id),

which is good because one to one mapping should check this, and is correct with ANSI:

According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values.

and this unique constraing is consistent with definition of one to one mapping:

A one-to-one relationship in a relational database occurs when one parent record or field has either zero or one child record only.

Problem is with databases implementations.
mySQL (OK):

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

PosgreSQL (OK):

When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.

MS SQL Server 2012 (Invalid):

In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not...
https://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values

Therefore in MS SQL Server 2012 (and 2008) there can be only one NULL (not connected) entity.
For MS SQL Server (>=2008) there is a workaround about this:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

http://msdn.microsoft.com/en-us/library/ms190457.aspx
Adding this to DDL generator would make ebean platform independ for One-to-One mapping.

and to fix change is required for method:
com.avaje.ebeaninternal.server.ddl.CreateTableColumnVisitor.visitOneImported.
I think that the same should be with column unique - it should be database platform independed.

I know that I can set in DbDdlSyntax.setAddOneToOneUniqueContraint(false) to ignore this, but this is not the right (nice) solution.

(I didn't checked this on H2 so far)

[Ryszard-Trojnacki]

Updating my last entry.

H2 database is working as expected.

Also intereseting article: http://tech.pro/tutorial/1419/10-things-in-sql-server-which-don-t-work-as-expected

[rbygrave]

Fixed in master, rolling out with 6.3.1