Entity splitting not working correctly due to PK constraint naming conflict

[erick-thompson]

This is using the feature where some properties of an entity are mapped to a different table.

The core of the problem is that PK constraint names need to be unique in Postgres. The way that EF generates the PK constraint names results in conflicts when creating the database using EnsureCreated. There appears to be no way to mitigate this due to the way that names are generated.

The EnsureCreated path ends up calling RelationalKeyExtensions.GetName to get the name of the PK constraint. Because both the primary and split mapping have the same entity, and thus the same PrimaryKey, the name of the PK ends up identical on both tables, and the available override via annotation applies to the key, not the StoredObject. The net effect is that EF always creates the same name for both PK constraints, which fails as it's not unique.

The pgcontext.Database.Migrate path does not have the same issue, and there are multiple ways to customize the names.

The lack of a workaround is that there are no injection points to modify the name of the constraint at creation time, and the code doesn't allow for a table/StoredObject scoped constraint name.

I can provide sample code if that would help.

[roji]

Thanks, this is indeed a bug in the provider - EF does support enforcing uniqueness across things like primary keys, but the PG provider is incorrectly configured. I'll go ahead and fix this.

Uniqueness tests on primary key, foreign key and index names

-- PK names must be unique:
DROP TABLE IF EXISTS a;
CREATE TABLE a (id integer);
ALTER TABLE a ADD CONSTRAINT some_pk PRIMARY KEY (id);

DROP TABLE IF EXISTS b;
CREATE TABLE b (id integer);
ALTER TABLE b ADD CONSTRAINT some_pk PRIMARY KEY (id);
-- ERROR: relation "some_pk" already exists


-- Index names must be unique:
DROP TABLE IF EXISTS a;
CREATE TABLE a (id integer);
CREATE INDEX some_index ON a (id);

DROP TABLE IF EXISTS b;
CREATE TABLE b (id integer);
CREATE INDEX some_index ON b (id);
-- ERROR: relation "some_index" already exists

-- FK names don't need to be unique
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (id integer PRIMARY KEY);

DROP TABLE IF EXISTS a;
CREATE TABLE a (id integer, foo_id integer);
ALTER TABLE a ADD CONSTRAINT some_fk FOREIGN KEY (foo_id) REFERENCES foo (id);

DROP TABLE IF EXISTS b;
CREATE TABLE b (id integer, foo_id integer);
ALTER TABLE b ADD CONSTRAINT some_fk FOREIGN KEY (foo_id) REFERENCES foo (id);

[erick-thompson]

Excellent, thanks!

[alienwareone]

@roji One of my applications uses multiple schemas with tables that share the same primary key name, which PostgreSQL allows.

After upgrading to EF Core 10, migrations obviously try to drop and recreate these PKs, breaking foreign key constraints.

Instead of enforcing uniqueness across all tables, it would be great if SharedTableConvention also considered the schema.

DROP SCHEMA IF EXISTS a CASCADE;
DROP SCHEMA IF EXISTS b CASCADE;

CREATE SCHEMA a;
CREATE SCHEMA b;

CREATE TABLE a.a (id integer);
ALTER TABLE a.a ADD CONSTRAINT some_pk PRIMARY KEY (id);

CREATE TABLE b.b (id integer);
ALTER TABLE b.b ADD CONSTRAINT some_pk PRIMARY KEY (id);

FYI: Just replaced the NpgsqlSharedTableConvention with a custom implementation.

public sealed class ApplicationDbContext : DbContext
{
    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Conventions.Replace<NpgsqlSharedTableConvention>(
            x => ActivatorUtilities.CreateInstance<ApplicationSharedTableConvention>(x));
    }
}

public sealed class ApplicationSharedTableConvention(
    ProviderConventionSetBuilderDependencies dependencies,
    RelationalConventionSetBuilderDependencies relationalDependencies)
    : NpgsqlSharedTableConvention(dependencies, relationalDependencies)
{
    protected override bool KeysUniqueAcrossTables
        => false;

    protected override bool ForeignKeysUniqueAcrossTables
        => false;

    protected override bool IndexesUniqueAcrossTables
        => false;

    protected override bool CheckConstraintsUniqueAcrossTables
        => false;
}

[roji]

I see... So PK constraint names are scoped to their schema - it's OK to have the same constraint name for two tables, just as long as they're not within the schema.

/cc @AndriySvyryd