Feature Request: Option to create a nullable unique index

[Arnagos]

I can't seem to find an option to create nullable unique indices.

I think it would be reasonable to create a nullable unique index whenever any column(s) of the index can be null.

Desired SQL:

CREATE UNIQUE NONCLUSTERED INDEX [Punchclock__Company_ContactInfoId_idx] 
    ON [Punchclock__Company]([ContactInfoId])
    WHERE [ContactInfoId] IS NOT NULL;

Currently, it just generates this SQL:

CREATE UNIQUE NONCLUSTERED INDEX [Punchclock__Company_ContactInfoId_idx] 
    ON [Punchclock__Company]([ContactInfoId]);

[martinjw]

This is Filtered indexes, which is SqlServer 2008+ but not sure if other dbs support (maybe function indexes?)

It will have to be quite limited (just a string where you have to add the WHERE sql).

[Arnagos]

Sure, generating this much myself should be no problem at all. Thanks!

[Arnagos]

I created a local NuGet package with your changes and tried it out. Works perfectly fine.

[Arnagos]

@martinjw Can you also add this feature to PostgreSQL? It's supported via partial indexes. The syntax seems to be the exact same as in MSSQL.

[Arnagos]

Sadly the change generates invalid SQL:

CREATE INDEX "X_idx" ON "Y"("A", "B", "C"); WHERE "C" IS NOT NULL

The ; is not removed from the end of the initial SQL and also not appended to the new ending.

[martinjw]

Fixed in https://github.com/martinjw/dbschemareader/releases/tag/2.10.1.0