CHECK约束

检查布尔表达式约束

• @Check = `price >= 100` 检查价格大于等于100
• @Check = `price > cost` 检查价格大于成本

• 布尔表达式用反引号字符 ` ` 定义
• 表达式语法: https://expr-lang.org/docs/Language-Definition

示例

数据模型

hrm.salaries 工资表

CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
)

给 hrm.salaries 工资表 to_date 字段添加如下注释

检查约束 @Check=`date(to_date) > date(from_date)` 结束日期大于开始日期

添加该注释的SQL

ALTER TABLE `hrm`.`salaries` CHANGE `to_date` 
`to_date` date NOT NULL COMMENT '检查约束 @Check=`date(to_date) > date(from_date)` 结束日期大于开始日期';

请求

POST http://127.0.0.1:21000
Content-Type: application/json;charset=utf-8

{
  "jsonrpc": "2.0",
  "method": "hrm.salaries.update",
  "params": {
    "empNo": 10010,
    "salary": 92429,
    "fromDate": "1996-11-24",
    "toDate": "1996-10-10"
  },
  "id": "client-unique-request-id"
}

响应

HTTP/1.1 400 Bad Request
Content-Type: application/json; charset=utf-8

{
  "jsonrpc": "2.0",
  "error": {
    "code": -32602,
    "message": "validating data type: check constraint failed",
    "data": {
      "errorCode": "EU.Validator.DataTypeError",
      "errorDetails": [
        {
          "constraint": "ExpressionReturnFalse",
          "expression": "date(to_date) > date(from_date)",
          "location": "hrm.salaries",
          "message": "boolean expression `date(to_date) > date(from_date)` return false"
        }
      ],
      "errorMessage": "check constraint failed"
    }
  },
  "id": "client-unique-request-id"
}

备注

上述配置可替代如下脚本

/* 检查 to_date > from_date */

/* 使用 dayjs 库来比较日期 */
const dayjs = require('dayjs');

/* 获取请求参数 */
const { data } = luwakVar.rpcArgs;

const fromDate = dayjs(data.fromDate);
const toDate = dayjs(data.toDate);

if (fromDate.isAfter(toDate)) {
    luwak.returnError("DATE_RANGE_IS_INCORRECT", "开始日期必须小于结束日期")
}

如果定义了 Check Point，会优先执行 JavaScript。

可以禁用 PrePersist 和 PreUpdate。