MySQL: Is it mandatory to create a `Unique Key` when you have 2 `Primary Keys` in a table?

Question

Please pay attention to the below step by step description.

1. I have 2 tables, Transaction and Transaction_Fee. The primary key of Transaction is idTransaction, which is an auto generated value. The primary key of Transaction_Fee is idTransaction_Fee which is also an auto generated value.

2. Every Transaction has a Transaction_Fee, therefor, the idTransaction is a foreign key of Transaction_Fee table.

3. Every Transaction can have only one Transaction_Fee

Now, I think I need to make both idTransaction and idTransaction_Fee as primary keys in Transaction_Fee table, because Transaction_Fee per Transaction cannot be duplicated.

Now my question is, since the idTransaction_Fee is going to have 2 primary keys, is it mandatory to create a Unique Key for Transaction_Fee ? The reason I am asking this is in some cases I have seen people creating Unique Keys when they have 2 Primary Keys in a table. But in my case, since the Transaction_Fee can be easily identified using its auto generated ID idTransaction_Fee, the Unique Key is not required, I think.

However I am willing to take expert advice before proceeding.

Answer 1

You do not ever have two primary keys in a single table, you have one by definition.

Your requirements seem to be:

• unique ID in the transaction table.
• unique ID in the fee table.
• up to one fee per transaction.

What you do in that case is create primary keys in both tables for their appropriate IDs, this will prevent duplicate IDs in those tables.

Then you need a unique constraint on the idTransaction column in the Transaction_Fee table. This will prevent two fees belonging to a single transaction by virtue of the fact no two fees can ever have the same transaction ID.

This column should also have a foreign key constraint, referencing Transaction(idTransaction), to ensure it points to a valid transaction (i.e., no orphaned fees).

That still allows the possibility of a transaction having no fee and there are also ways to ensure the relationship is truly one-to-one in both directions if that's what you want (exactly one fee per transaction rather than zero-or-one).

But, in that case, the fee would really seem to be a property of the transaction itself, and should probably go directly into the transaction table.