Postgresql 中的 check


在前面的例子中有提到了 check, 在 create table 時,可以直接加上 check 限制.
或是搭配前面的 domain ,建立 check. 這樣十分的靈活.

Postgrsql 部分文件請參考 
https://www.postgresql.org/docs/11/ddl-constraints.html
可以看到是分在 DDL 的 constraint, check 是 constraint 的一種.

MySQL 部分文件可以參考 
https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html
裡面有提到在 8.0.16 之前,check 在 MySQL中是無效,
僅僅是為了執行標準SQL Command 不會報錯,這很MySQL.
在 8.0.16 (含)以後,開始正式提供了 check 的功能.

在 MySQL 中, 有 int unsigned 系列,好幾種不同 size 的 integer,
都有分有號數,以及無號數.
https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

但在 Postgresql 中,基本資料型態中,並未提供無號數(unsigned).
因為在 Postgresql ,可以建立 domain, 例如我們要建立 無號數,範圍從 0~65535
可以這樣做

create domain uint2 as int4
 check(value >= 0 and value < 65536);

使用我們的老朋友 轉型, 來驗證

select 32769::uint2;
+-------+
| uint2 |
+-------+
| 32769 |
+-------+

select (-1)::uint2;

ERROR:  23514: value for domain uint2 violates check constraint "uint2_check"
SCHEMA NAME:  miku
DATATYPE NAME:  uint2
CONSTRAINT NAME:  uint2_check
LOCATION:  ExecEvalConstraintCheck, execExprInterp.c:3494

select 65536::uint2;
ERROR:  23514: value for domain uint2 violates check constraint "uint2_check"
略..

可以看到都有效的驗證,而且可以看到是呼叫了執行評估的內部函數(ExecEvalConstraintCheck)
還有 運算式直譯函數(execExprInterp), 對有興趣追蹤 source code的,也提供了線索.

接著來看看

select -1::uint2;
+----------+
| ?column? |
+----------+
|       -1 |
+----------+

為何這裡結果是 -1, 而上面的例子使用 (-1)::uint2, Domain 的 check 有效回報錯誤.
這就要理解運算子優先權.請參考文件中的 Table 4.2. Operator Precedence (highest to lowest)
https://www.postgresql.org/docs/11/sql-syntax-lexical.html#SQL-PRECEDENCE

可以理解到 :: 轉型運算子,優先權較高,會先執行,所以會得到這樣的結果.

check 還有一些有用的特性,check 是屬於 table 的 constraint, 所以可以驗證多個欄位.
check 也可以呼叫自定義函數,check 可以使用正規表示式來驗證輸入資料的格式.
靈活的使用,可以讓系統開發有極大的穩固性,而且是在資料庫層級執行,做最終防線的把關者.
以筆者以往開發的系統,舉例來說,員工的薪資,不得扣到為負,就可以搭配 domain 及 check.
或是某些品項的購買數量有上限,這樣避免了某些程式檢查的漏洞,造成極大的損失.
或是某些金額可以制定範圍,例如充值上限等等.
使用 Domain 搭配 Check ,這是一個先進的資料庫產品,可以帶給我們系統安全,有效確保正確
邏輯的運行,並很容易更新.減少因為漏掉改了某隻程式,而造成錯誤資料進入系統,進而造成更大的損失.

一般常見的資料型態,相信大家都已經很熟悉,所以不管是使用一般運算式,或是正規表示式,來做check,
在此就不多舉例.接著是比較特殊的例子.

create domain lifespan as daterange check (
 (not lower_inf(value)) and (
    upper_inf(value) or
    (upper(value) - lower(value) < 365 * 150)
 )
);

select '[2019-09-02,)'::lifespan;
+---------------+
|   lifespan    |
+---------------+
| [2019-09-02,) |
+---------------+

select '[,2019-09-02)'::lifespan;
ERROR:  23514: value for domain lifespan violates check constraint "lifespan_check"

select '[1900-01-01,2019-09-02)'::lifespan;
+-------------------------+
|        lifespan         |
+-------------------------+
| [1900-01-01,2019-09-02) |
+-------------------------+

select '[1800-01-01,2019-09-02)'::lifespan;
ERROR:  23514: value for domain lifespan violates check constraint "lifespan_check"
超出了150歲(概略).

這是使用了 range 資料型態,還有相關函數,這是 Postgresql 的一系列強大的資料型態,
先讓他上場表演一下,後續會再介紹.上面的例子,先不多做細部解釋.等介紹過 range 以後,
再來看,會更有感覺.