SQL primary key in lookup table or unique constraint?

Question

I want to create a lookup table 'orderstatus'. those. below, just to clarify this should be used in the data warehouse. I will need to connect via OrderStatus to retrieve the INT (if I create it), which will be used elsewhere if necessary. For example, as in a fact table, I would save the int in the fact table to refer to the lookup table.

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

What is the best practice in terms of primary key / unique key? Should I just create OrderStatusKey INT as PrimaryKey with id? Or create a unique order status restriction (unique)? Thank.

Answer 1

Identity .

, , - ( ).

, :

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

MSDN

Primary Key Primer

Answer 2

OrderStatus ( ), OrderStatus .

:

; . , , .

---

- , , int IDENTITY . OrderStatus, , .

---

, ( , , ).

Answer 3

, :

• PRIMARY KEY , NULL .
• UNIQUE KEY NULL ( ANSI) NULL s. ( SQL Server r )
• CLUSTERED INDEX , .
  • CLUSTERED INDEX ( null), SQL Server GUID .
  • SQL Server GUID , )
• , rowid .
• , , , ( , )
• .

PK ( ) ( , ). , (aka IDENTITY SQL Server).

ConnectionStatus, (OrderStatus) , .

, .

/ . OrderStatus ( ), NON CLUSTERED IDENTITY PK CLUESTERED UNIQUE OrderStatu. ( ), / .

, , PK (OrderKey OrderID) OrderStatus.

PK / .

: , , .

In addition, I would suggest adding a dummy value, which you can use if there is no set of statuses (and use it by default for all reference columns). Because it is not established, still status, right?