Skip to main content

Ranges

In Luwak, the verification of the value range is implemented through CHECK constraints. CHECK is a function in the SQL92 specification. MySQL 8.0.16 supports CHECK.

CHECK constraints are used to limit the validity of data in the table and ensure that the data conforms to specified conditions or rules. By using CHECK constraints, data integrity and business rules can be enforced at the database level to ensure that the data in the database meets specified conditions and requirements. This helps maintain data quality and security, reduces the insertion of erroneous data, and enhances database reliability.

Application scenarios​

  • Data range restrictions

    Ensure that the value of a numeric field is within a specific range, such as requiring that the sales price must be greater than zero, or that the age must be within a certain range.

  • Date range restrictions

    Limit the validity of date fields, such as ensuring that a birthday falls within a valid date range, or that the contract start and end dates are logically correct.

  • enumeration value

    The value of a restricted field can only be one of the predefined enumeration values, such as gender can only be "male" or "female", or status can only be "active", "paused" or "logged out".

  • Complex conditions

    Enforce complex logic conditions, such as ensuring that an order's delivery date is after the order creation date, or that a mobile phone number matches a specific format.

  • Data integrity

    Used to ensure data integrity, such as ensuring that a valid customer ID exists in the orders table, or that a supervisor ID in the employees table refers to a valid employee.

  • Business Rules

    Implement rules related to specific business needs, such as ensuring that returned quantities are no greater than inventory quantities, or ensuring that a news article's publication date is before the current date.

  • Security constraints

    Used for security purposes to ensure that only authorized users have access to specific data. For example, only allow certain user types (such as administrators) to access certain sensitive data.

  • User permission control

    In a multi-user environment, you can use CHECK constraints to restrict user access to specific data to ensure that users can only access the data for which they are authorized.

Example​

In the existing employee data table, the salary field records employee wages. The check value of this field must be greater than 1000.

CREATE TABLE employees (
     emp_no INT PRIMARY KEY,
     first_name VARCHAR(50),
     last_name VARCHAR(50),
     hire_date DATE,
     salary DECIMAL(10, 2),
     CHECK (salary > 1000)
);

Now add a piece of data, salary is less than 1000

luwak request message​

JSON-RPC request message

{
   "jsonrpc":"2.0",
   "method":"hrm.employees.add",
   "params": [
     {
       "empNo": 10008,
       "birthDate": "1964-06-02",
       "firstName": "Thomas",
       "lastName": "Simmel",
       "gender":"F",
       "hireDate":"1985-11-21",
       "salary":999
     }
   ],
   "id":"client-unique-request-id"
}

Response message​

{
   "jsonrpc": "2.0",
   "error": {
     "code": 10000,
     "message": "error create: salary: 999 does not validate as numeric",
     "data": "EU.Z.Query"
   },
   "id": "client-unique-request-id"
}

Luwak gives an error message that the salary verification fails and refuses to be written to the database.

Last updated on