Foreign_Key Validation after Importing Data


i want to import a Database from MSSQL to MySQL.
Therefor i worte a small programm using the approach mentioned here

When using a Schema not containing relationships, this procedure works fine and all data ist copied correctly. The problem is, that when accessing the Database with the main program (using the Schema with relationsships and the foreign_key_constraints) i only get errors informing me about foreign_key_fails.

When i use a schema containing relationships to copy the Data, it leaves out the foreign_keys.
Then i can use the main program on the Database but have no associations at all.

I'm sure, that it's the way Aurelius should behave and im sure i doing something fundamental wrong, but can't get my hands on the exakt problem.
How can i copy my data from Database to Database, containing all the Relationships?

Thanks in advance and kind regards,

Can you please be more specific about this:

"i only get errors informing me about foreign_key_fails."
"it leaves out the foreign_keys."
"Then i can use the main program on the Database but have no associations at all."

I don't understand exactly what do you mean by that. Thank you.

  • That is the error i get, when i try to start my application after i copied Data into the tables.
    Screenshot (3)

  • I create my Entities with the Data Modeler. In my first attempt i made the Entity-Class with all the Relationsships i need. So when my application starts, all the tables will be created and i'm able to update,insert,edit all tables through my application. That works fine.
    Now i wanted to fill the Database with already existing Data from a Database, not created with this application but having the same Tables and Fields like the one in my new Aurelius based Application.
    So i thought it would be a good idea, to write a "DataimporterApplication" which transfers Data from the old Database to the new one.
    When i used the Entity-Class with all the Relations, not all Data got copied.
    The Pictures below show the difference between the Data in the source Table and the Data in the target table. The Fields ending with "..._nr" are the foreign_keys (Maybe im using the wron terminology).

  • I attached two Entity classes which hopefully help me explaining my problem. The one was created with respect to the relationships (so with associations) and the other was created without any relationships.
    When i used the Entity Class without the relationsships with my "DataimporterApplication", all fields an Data gets copied to the target table (speaking with the pictures: kd_nr, Geraet_nr... etc is filled with the correct guids from the Source Table). But when i want to access the target Database with the freshly copied Data from my main application i get errors like in the first picture. I guess thats because i use the Entity-Class with Relationsships in my main Application.

EntWithConstraint.pas (18.6 KB)
EntWOConstraint.pas (12.4 KB)

Hopefully i could clearify my case a bit.
Thanks in advance

Make sure that:

  • all your ManyValuedAssociation attributes have the CascadeTypeAllRemoveOrphan as the cascade;
  • all your Association attributes have the CascadeTypeAllButRemove as the cascade.

You can do it manually for each association in Data Modeler, that will keep your model correct.
If you don't want to or can't do it manually, you can try to use customization scripts to change the cascade when the entities are generated:

So i changed the Entity-Class
My Associations looking like that

    [Association([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('Geraet_nr', [], 'nr')]
    FGeraet_nr: Proxy<TGeraet>;

and my Many-Valued-Associations like this

[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FReparatur_nr')]

Is that correct?
Because now the foreign_keys are still empty in the new DB.
I don't get the foreign_key_constraint fail though, but i'm clueless on what to try next.

To copy the Data i use the following code

  for I :=0  to Reparaturs.Count-1 do    ManagerLocal.Replicate(Reparaturs.Items[I]);

"Manager" is connected to the old Database, "ManagerLocal" to the new one.
The result is the same when using MangerLocal.Save (when the new DB is empty) instead of MangerLocal.Replicate.

I think I need you to build a small project reproducing the issue. Try to reduce your test scope to a minimum, reproduce the issue. You can use two SQLite databases to simplify it. Please send the test project to us so we can analyze and check what's going on, and suggest a fix for your code, or fix Aurelius code, if that would be the case.

So i made a Test Project from scratch and couldn't reproduce the problem.
I eventually checked my former application and found my problem:

I copied the Data with the Entity without Relationsships.
Afterwards i wanted to access the Data with a XData Server, which was based on the Entity with Relationsships after calling DBManager.UpdateDatabase;
Calling UpdateDatabase results in the Xdata forcing Foreign_key_Constraints into the mysql table which was created without any foreign_key_constraints.

So deleting the UpdateDatabase procedure helped to get rid of the Error messages but leaves me with another Question:
Will Xdata (Aurelius) manage Cascaded Removal dispite the missing foreign_key_constraints in the RDBMS?
So when i write an application, based on an Entity with relationsships, set the Relationship options accordingly, will the application mange the deletion or do i have to configure the RDBMS?

Kind regards,

Aurelius will perform the delete cascade manually regardless if the database has the foreign keys set or not.

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.