Scaffolding not working with two or more foreign key to the same table

[GreenEyedAndy]

Hi,
I tried to scaffold a existing database from a Firebird 3.0 Server with the FirebirdSql.EntityFrameworkCore.Firebird 8.5.2 provider.

When importing the following table:

CREATE TABLE KSC_MACRO_DEVICE_RESERVE (
    ID_MACRO_DEVICE_RESERVE  INTEGER NOT NULL,
    ID_EVENT                 INTEGER NOT NULL,
    ID_CIRCUIT_PAGE          INTEGER,
    ID_EVENT_MACRO           INTEGER,
    ID_CIRCUIT_PAGE_MACRO    INTEGER NOT NULL,
    X_SYMBOLPOS              INTEGER DEFAULT 0 NOT NULL,
    Y_SYMBOLPOS              INTEGER DEFAULT 0 NOT NULL,
    Z_SYMBOLPOS              INTEGER DEFAULT 0 NOT NULL,
    ORIENTATION              INTEGER DEFAULT 0 NOT NULL,
    GROUPINDEX               INTEGER DEFAULT 0 NOT NULL,
    COLOR                    INTEGER DEFAULT 8388736 NOT NULL
);



/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE KSC_MACRO_DEVICE_RESERVE ADD CONSTRAINT KSC_MACRO_DEVICE_RESERVE_C1 PRIMARY KEY (ID_MACRO_DEVICE_RESERVE);


/******************************************************************************/
/****                             Foreign keys                             ****/
/******************************************************************************/

ALTER TABLE KSC_MACRO_DEVICE_RESERVE ADD CONSTRAINT KSC_MACRO_DEVICE_RESERVE_C2 FOREIGN KEY (ID_EVENT) REFERENCES KSC_EVENT (ID_EVENT) ON DELETE CASCADE;
ALTER TABLE KSC_MACRO_DEVICE_RESERVE ADD CONSTRAINT KSC_MACRO_DEVICE_RESERVE_C3 FOREIGN KEY (ID_CIRCUIT_PAGE) REFERENCES KSC_CIRCUIT_PAGE (ID_CIRCUIT_PAGE) ON DELETE CASCADE;
ALTER TABLE KSC_MACRO_DEVICE_RESERVE ADD CONSTRAINT KSC_MACRO_DEVICE_RESERVE_C4 FOREIGN KEY (ID_EVENT) REFERENCES KSC_EVENT (ID_EVENT) ON DELETE SET NULL;
ALTER TABLE KSC_MACRO_DEVICE_RESERVE ADD CONSTRAINT KSC_MACRO_DEVICE_RESERVE_C5 FOREIGN KEY (ID_CIRCUIT_PAGE_MACRO) REFERENCES KSC_CIRCUIT_PAGE (ID_CIRCUIT_PAGE) ON DELETE CASCADE;

I get a "The foreign key {'ID_EVENT'} cannot be added to the entity type 'KSC_MACRO_DEVICE_RESERVE' because a foreign key on the same properties already exists on entity type 'KSC_MACRO_DEVICE_RESERVE' and also targets the key {'ID_EVENT'} on 'KSC_EVENT'." Error.

What can I do? I think there is a problem with the naming of more than 1 Key to the same table.

[cincuranet]

Your KSC_MACRO_DEVICE_RESERVE_C2 and KSC_MACRO_DEVICE_RESERVE_C4 are the same (only difference is ON DELETE rule). That's the problem.

[GreenEyedAndy]

I think that's because the KSC_MACRO_DEVICE_RESERVE is bound a.) to the KSC_EVENT where it was created and b.) to one or many other KSC_EVENTs where it is used.
In this database there are many tables with more than one foreign key to another table - it's always this x belongs to y but it has also another relationship to z.

[cincuranet]

If I understood that correctly, then you have a bug in your design. You'd need 2 columns for that to work. Because the FK does not know what "created" and "used" would be. It's just a link.

[GreenEyedAndy]

There are two columns ID_EVENT and ID_EVENT_MACRO.

[cincuranet]

But KSC_MACRO_DEVICE_RESERVE_C2 and KSC_MACRO_DEVICE_RESERVE_C4 both use ID_EVENT.

[GreenEyedAndy]

I issued that to my Database-Expert - and he admits you're right there is really something wrong! The second constrained must go to ID_EVENT_MACRO.
I try to fix that and give it a try.

[GreenEyedAndy]

Now it works. Sorry for the confusion!