CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designprimary-key
David542
David542

Reputation: 110083

One or Two primary keys for a database

I have two columns in a table that will always be unique, vendor_identifier and product_identifier. Both of them are about equal length. Should I add both of them as primary keys, or only one, or some variant of that? Is there any difference between adding one or two here?

Upvotes: 1

Views: 240

Answers (6)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

You can only have one be the primary key. You can have the other be a UNIQUE key.

Whichever you prefer to be the default PRIMARY KEY is your choice.

There is something you need to ask yourself:

  • Will a table that has both columns allow multiple products?
  • Will a table that has both columns allow multiple vendor?
  • Will a table that has both columns allow the tuple (vendor,product) one or more times?

Answering these rhetorial questions will help you decide whether a table has one of the following as the PRIMARY KEY

  • vendor_identifier
  • product_identifier
  • vendor_identifier,product_identifier

Upvotes: 0

Brett Rossier
Brett Rossier

Reputation: 3472

Assuming your vendor_identifier is a foreign key relating to a vendor table, and product_identifier is a foreign key relating to a product table, I'd create an autonumber field (vendor_product_identifier, perhaps?) to be the primary key of the table that has both vendor_id and product_id in it. Then I'd place a unique index on the combination of vendor_id and product_id.

So, the general idea would be:

Vendor
------
vendor_identifier PK
name
phone
etc...

Product
-------
product_identifier PK
name
category
etc...

Vendor_Product
--------------
vendor_product_identifier //"AUTONUMBER PK"
vendor_identifier //"FK to Vendor, and part of COMBOINDEX1"
product_identifier //"FK to Vendor, and part of COMBOINDEX1"
etc...

Having a new key for vendor_product gives you just one key to pass around on the application side to refer to a combination of both vendor and product. Having a unique index on the combination of vendor_id and product_id in the vendor_product table ensures that you won't get duplicate entries for that combination of data either (has to be a unique index though, not just an index).

Upvotes: 0

Randy
Randy

Reputation: 16677

since you dont describe your tables - i'm going to suggest that you actually have 3 tables here:

VENDOR
--------
vendor_id
other_cols

PRODUCT
---------
product_id
other_cols

VENDOR_PRODUCT
--------------
vendor_id
product_id
price-description-dates etc.

in this case - the VENDOR_ID in the VENDOR table is the PK. the PRODUCT_ID in the PRODUCT table is the PK (for that table)

the VENDOR_ID in the VENDOR_PRODUCT table is a foreign key the PRODUCT_ID in the VENDOR_PRODUCT table is a foreign key

you may choose to enforce uniqueness on the pair VENDOR_ID, PRODUCT_ID in the VENDOR_PRODUCT table, or not as you choose. If unique, they may be acting as a COMPOUND KEY in that table. If you need to reference rows in the VENDOR_PRODUCT from somewhere else in your schema, then you may consider a new single value primary key instead of copying these two columns to the new table and trying to get the FK definitions correct.

Upvotes: 0

Tim Gautier
Tim Gautier

Reputation: 30102

In MySql the primary key gets the clustered index, so you should make the primary key be the unique identifier you will most frequently query. (This includes joins.)

It's not quite clear from your question if those two fields are each unique on their own, or if they're only guaranteed to be unique as a combination. If they should always be unique individually, then at the least you should put a separate unique index on each of them. If they're only unique in combination, then that's your only guarantee of uniqueness and the primary key should be the two of them together as a single key.

Upvotes: 1

Mario
Mario

Reputation: 1851

Consider the following:

(1) is the combination of vendor_id and product_id also guaranteed to be unique?
(2) will you always search with both vendor_id and product_id?

A compound primary key only makes sense if you can answer yes to both. If you cannot, then just select the one with higher cardinality to be the primary key and make a secondary index on the other.

Upvotes: 0

Gabriel Sosa
Gabriel Sosa

Reputation: 7956

are que querying by both keys? or maybe one at the time?

depending on the answer you can do a composite index or two different indexes.. if you are adding two different indexes remember that the most used one should be at the left

but basically all depends of the architecture of your app / and the DB schema you choose to use...

Upvotes: 1

Related Questions