Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
RobJDavey opened this issue Jun 11, 2024 · 3 comments · May be fixed by #3412
Open

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

RobJDavey opened this issue Jun 11, 2024 · 3 comments · May be fixed by #3412
Assignees
Labels
enhancement New feature or request
Milestone

Comments

@RobJDavey
Copy link

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
Copy link
Member

roji commented Jun 11, 2024

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

@roji roji self-assigned this Jun 11, 2024
@roji roji added the enhancement New feature or request label Jun 11, 2024
@roji roji added this to the 9.0.0 milestone Jun 11, 2024
@WhatzGames
Copy link
Contributor

@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
Copy link
Member

roji commented Aug 15, 2024

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).

@roji roji modified the milestones: 9.0.0, 10.0.0 Oct 27, 2024
@WhatzGames WhatzGames linked a pull request Dec 18, 2024 that will close this issue
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
3 participants