1

Is it possible to have an unique property in the models that would allow multiple null values. Implementation is using SQL Server and it appears this would be possible: SQL Server UNIQUE constraint with duplicate NULLs

Is there any way to modify behavior of uniques constraints in CFE ?

Thanks for your answer,

Community
  • 1
  • 1
Olivier ROMAND
  • 579
  • 4
  • 15

1 Answers1

1

The SQL Server producer cannot generate this specific SQL statement. However you can replace the generated index by a filtered index.

If you need to change one or two indexes you can create a sql script named after_<default namespace>_tables.sql. This script will be automatically executed by the SQL producer (documentation).

-- TODO Drop unique index if exists
CREATE UNIQUE INDEX [IX_Cus_Cuo_Cus] ON [dbo].[Customer]([Customer_FullName])
WHERE [Customer_FullName] IS NOT NULL

If you need to change a lot of indexes, you can write a template and use the SQL Server Template producer (documentation):

[%@ namespace name="CodeFluent.Model"%]
[%@ namespace name="CodeFluent.Model.Persistence"%]
/* [%=Producer.GetSignature()%] */
[%foreach(Table table in Producer.Project.Database.Tables) { if (table.Constraints.Count == 0) continue;%]
[%foreach(CodeFluent.Model.Persistence.Constraint constraint in table.Constraints) { if (constraint.ConstraintType != ConstraintType.Unique) continue; %]

-- TODO Drop unique index if exists    
CREATE UNIQUE INDEX [[%=constraint.ShortName%]] ON [[%=CodeFluent.Producers.SqlServer.SqlServerProducer.GetOwner(table)%]].[%=table.FullName%] (
[%for(int i = 0; i < constraint.Columns.Count; i++) {%]
[%if(i != 0){%], [%}%][[%=constraint.Columns[i].Name%]]
[%}%]
)
WHERE [%for(int i = 0; i < constraint.Columns.Count; i++) {%]
[%if(i != 0){%]AND [%}%][[%=constraint.Columns[i].Name%]] IS NOT NULL
[%}%] 
[%}%]
[%}%]
meziantou
  • 20,589
  • 7
  • 64
  • 83