I've two posts inside a table which will always be unique, vendor_identifier and product_identifier. Are both about equal length. Must I add each of them as primary secrets, or just one, or some variant of this? Can there be any distinction between adding a couple of here?

are que querying by both secrets? or possibly one at that time?

with respect to the answer you are able to perform a composite index or two different indexes.. if you're adding two different indexes keep in mind that the most famous you ought to be at the left

but essentially all is dependent from the architecture of the application / and also the DB schema you decide to use...

In MySql the main key will get the clustered index, which means you should result in the primary key function as the unique identifier you'll most often query. (Including joins.)

It's less than obvious out of your question if individuals two fields are each unique by themselves, or maybe they are only certain to be unique like a combination. When they ought to always be unique individually, then at the very least you need to put another unique index on all of them. If they are only unique together, then that's your only guarantee of originality and also the primary key ought to be the pair of them together like a single key.

because you dont describe your tables - i am likely to claim that you really have 3 tables here:

VENDOR
--------
vendor_id
other_cols

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

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

within this situation - the seller_ID within the VENDOR table may be the PK. the merchandiseIdentification within the PRODUCT table may be the PK (for your table)

the sellerIdentification within the VENDOR_PRODUCT table is really a foreign key the merchandiseIdentification within the VENDOR_PRODUCT table is really a foreign key

you might want to enforce originality around the pair VENDOR_ID, PRODUCT_ID within the VENDOR_PRODUCT table, or otherwise while you choose. If unique, they might be serving as a substance Type in that table. If you want to reference rows within the VENDOR_PRODUCT from elsewhere inside your schema, you might think about a new single value primary key rather than copying both of these posts towards the new table and looking to get the FK definitions correct.

You are able to have only one function as the primary key. You could have another be considered a UNIQUE key.

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

There's something you require to request yourself:

  • Will a table which has both posts allow multiple items?
  • Will a table which has both posts allow multiple vendor?
  • Will a table which has both posts permit the tuple (vendor,product) a number of occasions?

Responding to these rhetorial questions can help you decide whether a table has among the following because the PRIMARY KEY

  • vendor_identifier
  • product_identifier
  • vendor_identifier,product_identifier

Think about 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 substance primary key only is sensible if you're able to answer yes to both. If you fail to, then just choose the main one with greater cardinality to become the main key making a secondary index alternatively.