Hello,

I’ve tried to define a relationship between two entities (A and B) on SQLServer like this : Entity A is linked to B by the BEntityId field but B is optional.

With this, LLBL creates a Unique Constraint on BEntityId : that causes a ‘duplicate key exception’ if I try to insert more than one record in A with BEntityId = NULL. Is any way to : 1) Avoid UC creation by LLBL ? (I've tried to check 'Model only' without success.) 2) Tell LLBL to generate an index on BEntityId with where clause is not null

(CREATE UNIQUE NONCLUSTERED INDEX idx_notnull ON dbo.TABLE_A(BEntityId ) WHERE BEntityId  IS NOT NULL;)

I’ve found informations on http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=18895&HighLight=1 and http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20203

Thanks in advance.

LLBLGen Pro version : 4.0 Final (July 10th, 2013) C# .Net FrameWork 4 on VisualStudio 2012 Database : SQLServer 2012

Best regards, Maxime.

I’ve tried to define a relationship between two entities (A and B) on SQLServer like this

Are you going DB first or Model first?

Walaa wrote:

I’ve tried to define a relationship between two entities (A and B) on SQLServer like this

Are you going DB first or Model first?

Hello Walaa, I'm in Model-first approach.

If the relationship is marked 'model-only', the FK and UC aren't created. I recon you want the FK but not the UC?

At the moment, the workaround is to alter the DDL SQL script and change the UC definition to something which does allow multiple null values on sqlserver.

Otis wrote:

If the relationship is marked 'model-only', the FK and UC aren't created. I recon you want the FK but not the UC?

Hello Otis, yes, I don't want the UC on it.

For the 'model-only' option, I do this : 1) Mark relationship as NOT model-only, check model and mapping validity, build source code and finaly 'Generate Database Shema Create script'. => Script1.sql 2) Mark relationship as model-only, check model and mapping validity, build source code and finaly 'Generate Database Shema Create script'. => Script2.sql

The only difference between this two scripts is the generation date. I can see in both scripts :

ALTER TABLE [dbo].[TABLE_A] 
    ADD CONSTRAINT [UC_0f79f5a4084834a8a01c643225c] UNIQUE NONCLUSTERED
    (
        [BEntityId] 
    )
GO

ALTER TABLE [dbo].[TABLE_A] 
    ADD CONSTRAINT [FK_1385efb45a6818f47c525a70283] FOREIGN KEY
    (
        [BEntityId] 
    )
    REFERENCES [dbo].[TABLE_B]
    (
        [Id] 
    )
    ON DELETE CASCADE
    ON UPDATE NO ACTION
GO

Otis wrote:

At the moment, the workaround is to alter the DDL SQL script and change the UC definition to something which does allow multiple null values on sqlserver.

Is any development on this subject regarding : http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20203 ? (scheduled fot v3.5)

Thanks

The UC in the model-only DDL SQL script suggests you have defined a UC on the BEntityId field in the entity ?

You don't have to do that, the UC is implicit.

We decided not to fix this, as it's hard to do: there's no consensus how to fix this for SQL Server, as there are multiple ways to fix it in the DDL SQL. So we left it to the user, to change the script to have a UC definition which allows nulls. As this can be done in different ways, all with their own side effects, we decided we can't pick one over the other.

Not emitting the UC in the DDL SQL would mean the relationship is actually a 1:n and allows multiple times the same FK value being present, something you also don't want as it will break at runtime.

So the 'best' way, IMHO, is to define a UC on the field, but an UC which allows null values, using one of the ways which one can work around this issue in SQL Server.

You could automate this, by using a different template for the UC creation snippet. It's the template bound to the id SD_DDLSQL_CreateUniqueConstraintInclude, see the sql server specific DDL SQL template bindings in the designer by opening the template bindings viewer and then selecting DDL SQL as framework.

The current template now simply defines a simple UC. You could extend this by checking whether a field in the UC is nullable. If so, emit a UC definition which can deal with that.

The best way to do this is in your own version of the template and then creating your own templatebindings file which overrides this defined template binding to id SD_DDLSQL_CreateUniqueConstraintInclude. If you need help with that, let us know

Otis wrote:

The UC in the model-only DDL SQL script suggests you have defined a UC on the BEntityId field in the entity ?

You don't have to do that, the UC is implicit.

I don't know if it's the same thing but if I open Edit panel on EntityA, The "Unique constraint" sub-panel is completly empty. I don't add any UC on this entity.

Otis wrote:

We decided not to fix this, as it's hard to do: there's no consensus how to fix this for SQL Server, as there are multiple ways to fix it in the DDL SQL. So we left it to the user, to change the script to have a UC definition which allows nulls. As this can be done in different ways, all with their own side effects, we decided we can't pick one over the other.

Not emitting the UC in the DDL SQL would mean the relationship is actually a 1:n and allows multiple times the same FK value being present, something you also don't want as it will break at runtime.

So the 'best' way, IMHO, is to define a UC on the field, but an UC which allows null values, using one of the ways which one can work around this issue in SQL Server.

You could automate this, by using a different template for the UC creation snippet. It's the template bound to the id SD_DDLSQL_CreateUniqueConstraintInclude, see the sql server specific DDL SQL template bindings in the designer by opening the template bindings viewer and then selecting DDL SQL as framework.

The current template now simply defines a simple UC. You could extend this by checking whether a field in the UC is nullable. If so, emit a UC definition which can deal with that.

The best way to do this is in your own version of the template and then creating your own templatebindings file which overrides this defined template binding to id SD_DDLSQL_CreateUniqueConstraintInclude. If you need help with that, let us know

Ok, thanks. I'll try to implement my own template... I will contact you if needed.

Maxime wrote:

Otis wrote:

The UC in the model-only DDL SQL script suggests you have defined a UC on the BEntityId field in the entity ?

You don't have to do that, the UC is implicit.

I don't know if it's the same thing but if I open Edit panel on EntityA, The "Unique constraint" sub-panel is completly empty. I don't add any UC on this entity.

Ok.

So to be clear: - initially you didn't have an FK and UC defined on the table in the relational model data - you define the relationship, 1:1 - you select 'validate and update relational model data' and the FK and UC are created - you change the relationship to model only - you validate again. This removes the FK (it marks it 'deleted') but keeps the UC?

This to check whether we can avoid having that UC show up in the DDL SQL for this scenario.

Otis wrote:

So to be clear: - initially you didn't have an FK and UC defined on the table in the relational model data YES - you define the relationship, 1:1YES - you select 'validate and update relational model data' and the FK and UC are createdYES - you change the relationship to model onlyYES - you validate again. This removes the FK (it marks it 'deleted') but keeps the UC? No

This to check whether we can avoid having that UC show up in the DDL SQL for this scenario.

I've followed this steps but FK and UC still here. Please, see attached file.

I walked through the steps and indeed can reproduce it. Then I went back to the design docs to see whether this is and it's because there's no direct connection between relationship in the model and the FK in the relational model data. So it doesn't actively remove FKs if you make a model relationship 'model only', there's no direct connection between the two so it can't say for sure 'that FK is made by me, I'm now changed to model only, so the FK can go'. Perhaps you even want to keep it so it's not actively removed. (another model element might have caused the FK to be there for example, when two entities are mapped onto the same table)

I can understand this is confusing. The switch to model only could indeed leave FKs and UCs dangling in the DB while they're created solely as backing for a relationship which doesn't need their backing anymore. I'll add a workitem to v4.2 worklog to see whether we can add a special case check for this situation to the validation so it removes these FKs/UCs. This then only occurs if the FK/UC is created by the designer (there's a flag on the fk/uc object for that, so it knows that), and the FK/UC was created to back a relationship which no longer needs it, and no other model element requires that FK/UC to be present (as multiple entities on the same table could lead to that situation).

Otis wrote:

The current template now simply defines a simple UC. You could extend this by checking whether a field in the UC is nullable. If so, emit a UC definition which can deal with that.

Another question for you: In my custom template, how to check if it's a nullable field ?

uniqueConstraint.Fields.??? == null

Maxime wrote:

Otis wrote:

The current template now simply defines a simple UC. You could extend this by checking whether a field in the UC is nullable. If so, emit a UC definition which can deal with that.

Another question for you: In my custom template, how to check if it's a nullable field ?

uniqueConstraint.Fields.??? == null

uniqueConstraint.Fields[0].IsNullable works, sorry

This is my override of SD_DDLSQL_CreateUniqueConstraintInclude :

  <%
 foreach (var item in uniqueConstraint.Fields)
{
    if (item.IsNullable == true && item.IsForeignKey == true)
    {
        __outputWriter.Write("CREATE UNIQUE NONCLUSTERED INDEX idx_" + constraintName + "_notnull ON [" + schemaName + "].[" + tableName + "](" + item.FieldName + ")WHERE " + item.FieldName + " IS NOT NULL ");
    }
}

var myFieldList = uniqueConstraint.Fields.Where(f => f.IsNullable == false || f.IsForeignKey == false).ToArray();
if (myFieldList.Count() > 0)
{
    __outputWriter.Write("ALTER TABLE [" + schemaName + "].[" + tableName + "] \r\n ADD CONSTRAINT [" + constraintName + "] UNIQUE NONCLUSTERED\r\n (\r\n " + string.Join(", ", myFieldList.Select(f => string.Format("[{0}]", f.FieldName)).ToArray()) + "\r\n)\r\n GO");
}
 %>

But it needs improvement...

Thanks for the feedback.