I'm migrating data from an existing proprietary database (accounting software) to MySQL. To do this, I linked the tables from the accounting software to MS Access using an ODBC driver, then exported each one to a table in my MySQL database via ODBC. This works great except that the existing indexes don't come over to MySQL. I added the indexes in MySQL using the following:
ALTER TABLE ap_invoice_detail ADD UNIQUE (supplier, invoice, entry_num, record_type_10);
So far, so good... but I won't be on the planet long enough to finish this task.
Enter Navicat... thanks to a recommendation from this site, I found Navicat which will allow me to set up the import of multiple tables to run unattended. Yay!
However, importing the data using Navicat creates different results from the above method and this brings up a question for me about use of primary key vs. unique index.
- What is the difference between using a primary key vs. using a unique index as above?
- Anyone have any thoughts about when to use which, if one is "better" than the other?
I decided on a unique index because I wanted to use it to prevent duplicates when I merge 2 versions of this data, but it looks like Navicat has a built-in way to handle that in the import wizard.
Many thanks for your help with this!