We help IT Professionals succeed at work.

MySQL - primary key vs. add unique index

monkeybiz12345
monkeybiz12345 used Ask the Experts™
on
Greetings Experts!

Greetings!

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!


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL

MySQL doesn’t let you define a primary key over nullable columns, for this reason. This is as of version 4.0, I believe — I recall that in version 3.23 there was nothing special about a primary key; it was just a unique non-nullable index named PRIMARY.

It depends on your structure which one to use.

Hope this helps.

Author

Commented:
Thanks for your comments, PranjalShah.  

After posting this question, I did some further testing on my own data and found this:

When a PRIMARY KEY is used, I cannot add a record to my table if one of the fields defined in the primary key has no value.  For example, the table in the accounting system often has filler fields as part of the index.  Most of the time, these are empty.  For example, the Banks file contains the following fields:

BANK_CODE = 100
RECORD_TYPE_20 = 20
FILLER1 =
BANK NAME = MY BEST BANK
GL_ACCOUNT = 12345

The accounting system currently defines the index as BANK_CODE+RECORD_TYPE_20+FILLER1.  The field FILLER1 is empty for all records in the file.  

If I import the file and define a PRIMARY KEY, it imports existing records that have no data in the FILLER1 field, but when I try to add a new record to that file, it requires data in the FILLER1 field.

If I define a UNIQUE index, it allows addition of a new record where the FILLER1 field is empty.

It's almost as if PRIMARY KEY has an element of "this is a required field that must be filled with data".


You have to have a value in a fields defined in the primary key. At any time primary key can not be null and also there cant be any duplicate values in the primary key field.

Author

Commented:
PranjalShah,

re: your comment

"You have to have a value in a fields defined in the primary key. At any time primary key can not be null and also there cant be any duplicate values in the primary key field. "

Am I correct in my thinking that this is not the case for UNIQUE indexes, that empty fields are allowed as part of UNIQUE indexes?

If so, since I know that there will be fields that are part of the key that will be empty (whether I've actually got a NULL value or an empty string is entirely another issue), I should use UNIQUE instead of PRIMARY KEY.
From my first comment: "a UNIQUE index allows multiple NULL values for columns that can contain NULL"

so yes, if you know there will be null values for your indexes use unique instead of primary.

Author

Commented:
Thanks for the clarification.