Leaderboard

The order you want depends mostly on how you'll be using the index in your queries. I'd venture that in an order system you're most likely going to be joining orders to the details then to the products to generate invoices and such, which would have queries like: select * from orders o inner join order_details od on od.order_id=o.id inner join products p on p.id=od.product_id A query such as that would want your UK defined as (order_id,product_id). With that order the UK can be used to enforce your foreign key relationship on the order_id column and be used to speed up the join between the orders and order_details tables. If on the other hand you were doing a query based on products, such as to find popular products you might do queries like: select * from products p inner join order_details od on od.product_id=p.id For that kind of query you might consider the (product_id,order_id) order so you could use the UK to handle your product_id foreign key and the join to the products table. In either case, assuming both order_id and product_id are foreign keys then your UK can satisfy the index requirement for one of them and the other would need it's own index. Neither order is particularly bad or good in this situation as you need at least two indexes anyway. In such a table design you don't necessarily even need your ID column so you could just make your PRIMARY KEY be (order_id, product_id). Some people prefer always having a auto_increment primary key but in the scenario proposed it's not necessary and could be removed. More indexes mean more index management and disk space usage. That can potentially lead to slower performance when inserting/updating/deleting data, but wouldn't have any significant impact on selects. As such, you should try and limit your indexes to only those that you absolutely need to make your system function well. Until your system grows to a very large scale (millions of rows), it's unlikely you'd notice any problems from extra indexes though. There's no efficiency to be gained from having the separate PK and UK there as far as I am aware. A single multi-column primary key would be just as effective and save an index.

The foreign key constraint needs a suitable index on the column, but it doesn't need to be an index dedicated to that column. So yes, you can double up your unique index if you create it properly. In order to re-use it, the foreign key column must be the first column in the index, so when you create it you want to do CONSTRAINT UQ_blah UNIQUE (product_type_code, id) and not CONSTRAINT UQ_blah UNIQUE (id, product_type_code). As mentioned though, in the scenario you laid out, the unique constraint is entirely unnecessary as your ID column is already unique by virtue of being the primary key so you'd just make a simple index on the product_type_code column. In a scenario where Id wasn't a primary key and a unique constraint was necessary then you could double-dip like that.

Haha, Well nobody said modern system development was easy.