CHECK constraints

Check boolean expression constraints

• @Check = `price >= 100` Check if the price is greater than or equal to 100
• @Check = `price > cost` checks that the price is greater than the cost

• Boolean expressions are defined using the backtick character ` `
• Expression syntax: https://expr-lang.org/docs/Language-Definition

Example

Data model

hrm.salaries salary schedule

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)
)

Add the following comments to the to_date field of the hrm.salaries salary table

Check constraint @Check=`date(to_date) > date(from_date)` end date is greater than start date

SQL to add this comment

ALTER TABLE `hrm`.`salaries` CHANGE `to_date`
`to_date` date NOT NULL COMMENT 'Check constraint @Check=`date(to_date) > date(from_date)` end date is greater than start date';

ask

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"
}

Response

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"
}

Remark

The above configuration can replace the following script

/* Check to_date > from_date */

/* Use dayjs library to compare dates */
const dayjs = require('dayjs');

/* Get request parameters */
const { data } = luwakVar.rpcArgs;

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

if (fromDate.isAfter(toDate)) {
     luwak.returnError("DATE_RANGE_IS_INCORRECT", "Start date must be less than end date")
}

If Check Point is defined, JavaScript will be executed first.

PrePersist and PreUpdate can be disabled.