postgresql 枚举
技术评估 (Technical Evaluations)
It is a common situation where the value of a field is restricted to a list of valid options.
在一种常见的情况下,字段的值仅限于有效选项的列表。
For examples,
举些例子,
Genders:
male
,female
, …性别 :
male
,female
,…Currencies:
USD
,SGD
,CAD
,AUD
, …货币 :
USD
,SGD
,CAD
,AUD
…Countries:
us
,uk
,sg
… (200+ options)国家 :
us
,uk
,sg
...(200+选项)Statuses:
pending
,processing
,failed
,completed
, …状态 :
pending
,processing
,failed
,completed
...Genres:
romance
,comedy
,action
, …类型 :
romance
,comedy
,action
……
数据验证和完整性 (Data validation and integrity)
Although data validation can be done at the APPLICATION LOGIC LAYER either
尽管可以在APPLICATION LOGIC LAYER上完成数据验证
- on the client-side (form validations) 在客户端(表单验证)
- OR on the server-side (API or model validations), 或在服务器端(API或模型验证),
such validations can be bypassed by going straight to the lower layer.
可以直接进入下一层来绕过此类验证。
The safer way to ensure data integrity is by using DATABASE LEVEL CONSTRAINTS or VALIDATIONS.
确保数据完整性的更安全方法是使用“ 数据库级别约束”或“ 验证” 。
This article would be discussing 4 techniques in which we can restrict the values to a set of valid options in PostgreSQL (also applicable to other databases)
本文将讨论4种技巧 ,其中我们可以将值限制为PostgreSQL中的一组有效选项(也适用于其他数据库)
Enumerated Types
枚举类型
Simple CHECK constraints
简单的CHECK约束
CHECK constraints with FUNCTION
使用FUNCTION检查约束
FOREIGN KEYS
外键
Each of the approaches has distinguished PROS, CONS, and therefore, is suitable for different use cases.
每种方法都具有不同的PROS和CONS ,因此适用于不同的用例 。
评估方面 (Evaluation aspects)
ReadabilityHow easy to read and understand the validation
可读性 多么容易 阅读 和 理解 验证
UsabilityHow easy to retrieve the list of options.The number of options