Hi all,

we are using: MS Win 7 SP 1, MS Visual Studio 2010 SP 1, .NET Framework 4.0, LLBLGen Pro 3.1 newest release, MS SQL Server 2008 R2, MS Entity Framework 4.0.

This is the situation: Entities A and B are connected through a [0..1] to [0..1] relation. While in entity A the connector is the primary key attribute, in entity B it is a foreign key attribute which is optional.

For this relation I would expect the generation of an UNIQUE INDEX WHERE FOREIGNKEYATTRIBUTE IS NOT NULL. Instead an unique constraint without any where clauses are generated in such situations.

My question is: What do I have to do to get the correct result from generating database sql scripts?

Thank you in advance, regards

Chris

Entities A and B are connected through a [0..1] to [0..1] relation. While in entity A the connector is the primary key attribute, in entity B it is a foreign key attribute which is optional.

For this relation I would expect the generation of an UNIQUE INDEX WHERE FOREIGNKEYATTRIBUTE IS NOT NULL

But if the relation is optinal, it should be nullable, right?

I do not know if I understand Your question. The foreign key attribute IS optional. So it should be no problem at all if I create for example 10 rows without a value for the foreign key attribute ([ForeignKeyAttribute] = NULL. But when values are created for the [ForeignKeyAttribute], they have to be unique. Since MS SQL Server 2008, there is the possibility to create a where clause for the CREATE UNIQUE INDEX statement and this is what I expect to be generated in the DDL script).

This is currently not implemented, it will always generate a UNIQUE CONSTRAINT, which indeed forces on sqlserver not to use NULL values.

One workaround is to alter the generated script to allow nulls for the unique constraint by defining a unique index instead of the unique constraint. It's scheduled to be fixed in v3.5. It's SQL Server specific however, as sqlserver sees 'null' as a value, while that's debatable.

One downside of this workaround is that refreshing the catalog will not see the unique constraint as being there.

Another workaround is to mark the relationship as 'model only'. This won't generate the unique constraint in the DB. You can then define the unique index for referential integrity. I'd go for this workaround.