SQL primary key on lookup table or unique constraint?

Question

I want to create a lookup table 'orderstatus'. i.e. below, just to clarify this is to be used in a Data Warehouse. I will need to join through OrderStatus to retrieve the INT (if i create one) to be used elsewhere if need be. Like in a fact table for example, I would store the int in the fact table to link to the lookup table.

+------------------------+------------------+
| OrderStatus            | ConnectionStatus |
+------------------------+------------------+
| CLOSED                 | APPROVE          |
+------------------------+------------------+
| COMPLETED              | APPROVE          |
+------------------------+------------------+
| FULFILLED              | APPROVE          |
+------------------------+------------------+
| CANCELLED              | CLOSED           |
+------------------------+------------------+
| DECLINED               | CLOSED           |
+------------------------+------------------+
| AVS_CHECK_SYSTEM_ERROR | CLOSED           |
+------------------------+------------------+

What is best practise in terms of primary key/unique key? Should i just create an OrderStatusKey INT as PrimaryKey with identity? Or create a unique constraint on order status (unique)? Thanks.

Answer 1

For this, I would suggest you create an Identity column, and make that the clustered primary key.

It is considered best practice for tables to have a primary key of some kind, but having a clustered index for a table like this is the fastest way to allow for the use of this table in multi table queries ( with joins ).

Here is a sample as to how to add it:

ALTER TABLE dbo.orderstatus 
ADD CONSTRAINT PK_orderstatus_OrderStatusID PRIMARY KEY CLUSTERED (OrderStatusID);
GO

Article with more details MSDN

And here is another resource for explaining a primary key Primary Key Primer

Answer 2

If OrderStatus is unique and the primary identifier AND you will be reusing this status code directly in related tables (and not a numeric pointer to this status code) then keep the columns as is and make OrderStatus the primary clustered index.

A little explanation:

A primary key is unique across the table; a clustered index ties all record data back to that index. It is not always necessary to have the primary key also be the clustered index on the table but usually this is the case.

---

If you are going to be linking to the order status using something other than the status code then create another column of type int as an IDENTITY and make that the primary clustered key. Also add a unique non-clustered index to OrderStatus to ensure that no duplicates could ever be added.

---

Either way you go every table should have a primary key as well as a clustered index (again, usually they are the same index).