question

EuroEager2008-7858 avatar image
0 Votes"
EuroEager2008-7858 asked DanielZhang-MSFT commented

Code first Unique constraint on multiple columns

Say I want a Microsoft SQL Server entity as follows:
CREATE TABLE [dbo].[Entity](
[ID] [int] IDENTITY(1,1) NOT NULL,
[COL1] [nvarchar](450) NOT NULL,
[COL2] [nvarchar](450) NULL,
[COL3] [nvarchar](450) NULL,
CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED

Then I want to use fluent API to create a constraint based on COL1, COL2 and COL3 such that rows like
"One",Null,Null (for COL1,COL2 and COL3) can only exist once (and of course COL1 cannot be null).
The constraint must be within the DB scheme, not just in EF core.
I have tried with both .HasAlternateKey() and .HasIndex().IsUnique(), but I can still add mentioned rows data.

dotnet-entity-framework-core
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your effort, however:

Your first alternative doesn't make any difference other than table name is now pluralized (not what I want) and the name of the index changes to "IX_Entities_COL1_COL2" instead of "UniqueCOL1_COL2".
The rows inserted by console program are exactly the same as before (what I consider not unique).

Your second alternative fails when adding migration due to the [] square brackets, removing the square brackets doesn't migrate either because my code explicitly defines the COL2 column as NOT required (which IS what I want).

Did you try your suggestions on my code?

0 Votes 0 ·

I have no idea why I cannot find your latest answer in this thread by the browser. My Outlook shows that you have answered as follows:

Hi @EuroEager2008-7858, I note that your model field is a string, please make sure it is not set to nvarchar(MAX) or varchar(MAX) in SQL Server. And your type of the unique attribute are all string, so have you set the MaxLength? Best Regards, Daniel Zhang

My reply: Sure, the migration ensures that the index fields (COL1 and COL2) are both set as nvarchar(450) by ef core (after update-database). Other than that, I don't really understand what you mean by "And your type of the unique attribute are all string, so have you set the MaxLength?"

The database was/is fully created by ef core, started with a drop-database and didn't touch it "manually"

0 Votes 0 ·

Hi @EuroEager2008-7858,
I noticed that you have setted the COL1 and COL2 to nvarchar(450), so I deleted my comment that your Outlook showed.
>>CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED
What is this statement used for? And I made a test with your project, this sql statement reports an error in the sql server.
COL2 allows null, so it can't be set primary key. So I change to CONSTRAINT PK_HISTORY PRIMARY KEY (COL1)).
Best Regards,
Daniel Zhang

0 Votes 0 ·
DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered EuroEager2008-7858 commented

Hi EuroEager2008-7858,
>>I have tried with both .HasAlternateKey() and .HasIndex().IsUnique(), but I can still add mentioned rows data.
In general, you can use HasIndex() and to add indexes for migration through fluent API. So in order to find the cause more accurately, please provide the code you tried.
Code likes below:

     modelBuilder.Entity<Blog>()
         .HasIndex(b => b.Url)
  .IsUnique();

And I found some code example using fluent API to create a constraint you can refer to.
Setting unique Constraint with fluent API?
Entity Framework Core add unique constraint code-first
Unique Key constraints for multiple columns in Entity Framework
Hope these are helpful to you.
Best Regards,
Daniel Zhang


If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have made a simplification with a 2-column index instead of 3.
Please download the simplified solution from here:
https://1drv.ms/u/s!AoFGuPCXh0GgidM1g8BZoMrvz9i26Q?e=r38nmM

Also note that I am using SQL server Express (14.0.3238).

Open the solution, add a migration, update db and run the console program.
Please explain why the resulting console output (added as a Result.txtfile in the project as well) shows that the first two rows only differs on ID's even though the "dual-column-index" is clearly designed with HasIndex().IsUnique(true).

Perhaps I don't really understand how SQL databases handle NULLS as part of index, but to me it is quire clear that the two rows COL1 and COL2 combination are not unique.

0 Votes 0 ·
DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered

Hi EuroEager2008,
Please try to change your code below



 modelBuilder.Entity<Entity>()
                 .ToTable("Entity")  // Is this really neccessary to singularize the entity name?
                 .HasIndex(entity => new { entity.COL1, entity.COL2 }, "UniqueCOL1_COL2").IsUnique(true);

to

 modelBuilder.Entity<Entity>()
    .HasIndex(entity => new { entity.COL1, entity.COL2 }).IsUnique();

or

 modelBuilder.Entity<Entity>().HasAlternateKey(entity => new [] {entity.COL1, entity.COL2}).HasName("UniqueCOL1_COL2");

Best Regards,
Daniel Zhang


If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.