Add support for foreign key match types (FULL, PARTIAL, SIMPLE)

[RobJDavey]

When creating a foreign key that references multiple nullable columns in Postgresql it's possible to add a MATCH expression on the foreign key to ensure that all values are either null, or are all required.

From the documentation:

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table. MATCH PARTIAL is not yet implemented.

It would be useful if this could be configured on the relationship in the model builder. An example of how this might look:

entity.HasOne(static e => e.Unit)
    .WithMany(static e => e.Rules)
    .HasForeignKey(static e => new
    {
        e.OwnerId,
        e.UnitId,
    })
    .HasPrincipalKey(static e => new
    {
        e.OwnerId,
        e.Id,
    })
    .HasMatchType(MatchType.Full); // new extension to support the match type

which would result in the DDL:

CONSTRAINT "FK_Rules_Units_OwnerId_UnitId" 
FOREIGN KEY ("OwnerId","UnitId") 
REFERENCES public."Units"("OwnerId","Id")
MATCH FULL

[roji]

Thanks, I'll try to get this in for 9.0.

[WhatzGames]

@roji If it's fine by you, then I'd like to take a shot at this. Any hints for what I'd need to keep in mind or pay attention to, are greatly appreciated.

[roji]

Sure. This is a migrations-only feature, so you'd need to introduce the metadata and builder APIs to configure this (probably as an enum that we'd expose), then flow that through to NpgsqlMigrationsSqlGenerator, and make sure the proper DDL gets generated there. Tests can be added to MigrationsNpgsqlTest. You can try picking some other migrations-only metadata API (like one of the configurations on index), check out the PR that introduced it, and follow that as inspiration (though I don't think we've done something on a foreign key before).