Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

How to deal with lack of PK information?

Help
Adam Stein
2008-07-02
2013-05-01
  • Adam Stein
    Adam Stein
    2008-07-02

    Running mdbtools v0.6-0.3.cvs20051109 on a Fedora 8 system.

    Since mdb-schema doesn't contain primary key information, I have to fake it for now.  Wondering if anybody has a better idea that what I'm doing.  I have no desire to run anything on Windows, so any ideas would have to work on Linux.

    The database I'm converting (from Jet4/MS Access to MySQL) contains tables that have no primary key fields.  Since the MySQL backend doesn't know how to create relationships yet, I use the Oracle backend and convert a few things from Oracle syntax to MySQL (amazingly enough, the foreign key statements don't have to be modified).

    This particular database uses '<name>ID' as the primary key field name, so I make the first field I find in a table ending in 'ID' to be a primary key field.  When I'm transferring data, I look for a 'Duplicate entry' error when doing an insert.  If I find this, I alert the user that this particular table (where the insert failed) needs to be put on the "don't create a primary key for this table" list (which I have as an option to the program).

    It would be nice if I could do this automatically (which probably can't be done until mdb-schema can give primary key info).  Has anybody found a better way of dealing with this?

    I don't have control of the MS Access database schema, so I can't add a primary key to those database that don't have one.

    Thanks for any suggestions.