Error(s), Warning(s) and Action(s) reported by ValidateDatabase.

[Olsen_Leif_Eirik]

During startup of my xData/Aurelius rest server I do a TDatabaseManager.ValidateDatabase.
I used to get a lot of errors/warning/actions, but reading the documentation and especially this: Data Modeler and Aurelius compatibility, I understood and was able to get rid of most of them. I am though, still left with 12 errors, 3 Warning and 3 Actions.
My question is about the Warnings and Actions.

The 3 Warnings and 3 Actions:

[11/30/2022 2:30:32 PM][Info] Warning: Foreign key: ItemPlug.FK_ItemPlug_Product_ProductID - Created. Existing data might be incompatible.
[11/30/2022 2:30:32 PM][Info] Warning: Foreign key: ItemCtrl.FK_ItemCtrl_Product_ProductID - Created. Existing data might be incompatible.
[11/30/2022 2:30:32 PM][Info] Warning: Foreign key: ItemModem.FK_ItemModem_Product_ProductID - Created. Existing data might be incompatible.
[11/30/2022 2:30:32 PM][Info] Action: Foreign key: dbo.ItemPlug.FK_ItemPlug_Product - Removed.
[11/30/2022 2:30:32 PM][Info] Action: Foreign key: dbo.ItemCtrl.FK_Product_ItemCtrl - Removed.
[11/30/2022 2:30:32 PM][Info] Action: Foreign key: dbo.ItemModem.FK_Product_ItemModem - Removed.

Out of about 100 tables in my db, there are 3 that uses inheritance. These table are ItemPlug, ItemCtrl & ItemModem, and they all inherit from table Product. All the actions reported by ValidateDatabase are related to these three tables it seems, thus I suspect it might have to do with the inheritance.

The three foreign keys that are reported to be removed, looks just fine to me.
Here is an example of 1 of the 3 tables involved. The DDL from db for the table ItemPlug looks like this:

CREATE TABLE dbo.ItemPlug (
    ProductID NVarChar(20) NOT NULL,
    "Size" Int,
    PlugTypeID Int NOT NULL,
    SlipsTypeID Int NOT NULL,
    PackerPlateID Int NOT NULL,
    Length Float,
    HardOD Float,
    SetMin Float,
    SetMax Float,
    Min1_5DBendID Int,
    Min3_0DBendID Int,
    Min5_0DBendID Int,
    DesignMAWP Float, 
    CONSTRAINT PK_ItemPlug PRIMARY KEY CLUSTERED (
      ProductID
    )
)
GO
ALTER TABLE dbo.ItemPlug WITH CHECK ADD CONSTRAINT FK_ItemPlug_Product FOREIGN KEY (ProductID)
  REFERENCES dbo.Product (ID)
  ON DELETE NO ACTION 
  ON UPDATE NO ACTION
GO

and the Product table looks like this:

CREATE TABLE dbo.Product (
    ID NVarChar(20) NOT NULL,
    ProductGroupID Int,
    Name NVarChar(100),
    SnmLink NVarChar(20),
    SerialNumProductID NVarChar(20),
    Assemble Bit NOT NULL,
    ItemCategoryID NVarChar(10),
    ItemTypeID Int,
    ItemStatusID Int,
    AX_ID NVarChar(20),
    Revision NVarChar(10),
    HasDataLogger Bit NOT NULL, 
    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (
      ID
    )
)

So my question is:
Why is that ValidateDatabase reports it will DROP the three FK's and apparently recreate them in this situation?

[wlandgraf (Wagner Landgraf)]

That's the case of the foreign key name. In your mapping, you should explicitly inform the name of the foreign key, otherwise Aurelius will build one dynamically, which is different from the one in database - that's why he is trying to drop the existing one (with the "correct" name) and trying to create a new one (with the dynamic name). Use something like this in the FProductId association field of your TItemPlug classes (I'm guessing the names):

[ForeignKey('FK_ItemPlug_Product')]
FProductId: Proxy<TProduct>

[Olsen_Leif_Eirik]

Yes, I was aware that Aurelius would use it's own names for foreign keys. So in TMS Data Modeler I already had this script:

procedure OnAssociationGenerated(Args: TAssociationGeneratedArgs);
begin
  Args.Field.AddAttribute('ForeignKey').AddRawArgument(      
    '''' + Args.DBRelationship.RelationshipName + '''');           
end;

The script helped me keep my names for all foreign keys (about 130++).

What I just noticed was that, for the three tables mentioned, TMS Data Modeler do not generate a [Association([....])] for the relationship, rather it generates a [PrimaryJoinColumn('....')]. This is, I assume, due to the fact the three tables are inherited from table Product. Thus my script for OnAssociationGenerated will not trigger.

Do you see a way for me automate keeping my names for the fk's in question, for the situation described? Or is this a case where I have to manually add that to the generated model code?

[wlandgraf (Wagner Landgraf)]

Ah, in this case, the problem is not even in TMS Data Modeler.
For now, there is no way to indicate a specific foreign key name for the foreign keys generated for joined tables inheritance (primary joins).

A feature request should be created so that Aurelius support it, then it would make sense to make TMS Data Modeler be able to customize it.

[Olsen_Leif_Eirik]

Thank you for confirming.

I'd be happy to add it as a feature request to Aurelius.