Search code examples
codefluent

Unique constraint allowing null values

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,

Solution

  • 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
[%}%] 
[%}%]
[%}%]