data migration unique primary key violation

1
I made a mew entity and started the server where mendix created a new empty table for it.then I inserted some data directly from oracle DB then I am trying to add new record from mendix but an error raised saying primary key violation exception. so I truncate the table and add the first record from mendix and it is working fine then I delete this record again and used the same insert script directly from oracle DB and then add new record from mendix successfully. Conclusion: if the first record is inserted from mendix , there is no problem with the data inserted from oracle DB. otherwise the first record inserted into table was directly from DB ,the problems would raise.
asked
3 answers
2

It's much better to use the db replication or excel import module.

Because there are several things you'll have to keep in mind when storing data in your database manually. You will have to replicatie the Runtime behaviour. These are the things I can think of at this moment (there might be other things I have forgotten)

  • Sequences/autonr's/internal id's

    When adding the records you have to make sure that the database is aware of the new records. When the Runtime inserts any new records it raises the sequences (the db name for autonr). In your description the sequence thinks it can start counting at 1 but there are already a lot of records in the table.
    After adding all the records you have to look for the sequence and set the index to the new. Where to find and how to update the sequence varies per database. Just google 'alter sequence [dbtype]'
    If you look in the table Bas refers to you'll find the name of the sequences, but the sequence names are pretty easy to read so you can guess which sequence belongs to which table.

  • Enumerations

    Make sure all the values of your enumerations are identical to the Name of the enumeration in your modeler file. These names are case sensitive.

  • Date's/timezones

    Keep in mind that the XAS stores your datetime values in UTC time. So when adding date's to your database think of the timezone in which your original values are stored and convert that time to UTC before/while adding them to the database.

answered
0

Don't insert data manually in your database yourself. This is something you should let the server handle. There are plenty of ways to import data into your application.

This could be caused by an autonumber sequence that is being kept in a separate table. But the potential issues you could get here are numerous, since any data integrity checks done by your model are not being executed.

Edit: What it comes down to is that there is no guarantee data is saved in a way you expect, there are many things you can overlook when doing manual inserts like that. This could even change between different versions of Mendix. For example, the attribute sequences are now stored in System_CB_AttributeSequences but this will be changed in 3.0.

With data integrity I mean checks in your model such as unique constraints or required constraints. Or things like associations that are filled in a way that doesn't correspond with your model.

I recommend using the database replication module you can find in the appstore.

answered
0

Thanks Bas for your answer,but please :

  1. how can I find that separate table to find a way to take care about autonumber sequence and what is the data integrity you mean?
  2. I prefer to insert the data manually with considering all the relations of tables because we have many many tables and we already prepared that script and it is working fine except with this error ,I think any other way will take much effort and time that we don't have.
  3. in case of there is no way to insert the data directly from database through executing our inserting script , what is the best and fastest way to do this data migration?
answered