SQL schema 設定 primary key 的 constraint

最近做的工作，我在產品的資料庫，新增了一張 table 。在 code review 時，被同事建議「要設定 primary key」。於是，我就順便研究了設定 primary key 的重要性及理由。

主要的原因如下：有設定 primary key 的 column ，其值必定是唯一的。目前設計的這個 table，它的 business logic 裡，有一個 column 它的值也是唯一存在的，不會有重複的值。既然 business logic 就隱含了 unique value 的概念，加上 Primary key 的 constraint 自然可以「讓錯誤看得出來是錯誤」。

而好的 Primary Key 該如何設定呢？
Good primary keys are essential to good database design. They let you query and modify each table row individually without changing other rows in the same table. When you evaluate candidates for a table's primary key, follow these rules:

• The primary key should consist of one column whenever possible.
• The name should mean the same 5 years from now as it does today.
• The data value should be non-null and remain constant over time.
• The data type should be either an integer or a short, fixed-width character.
• If you're using a character data type, the primary key should exclude differential capitalization, spaces, and special characters, which might be difficult to remember.