1

I am trying to validate a JSON column given a schema on a different table. Since a foreign key constraint is also set I can guarantee that a schema is assigned.

The following sql code is not valid on MySQL, however it should translate what I am attempting to solve:

CREATE TABLE modules(
    id INTEGER NOT NULL PRIMARY KEY,
    schema JSON NOT NULL
);

CREATE TABLE licenses(
    id INTEGER NOT NULL PRIMARY KEY,
    config JSON NOT NULL,
    module_id INTEGER NOT NULL,
    FOREIGN KEY (module_id) REFERENCES modules(id),
    CHECK(JSON_SCHEMA_VALID(SELECT schema FROM modules WHERE id = module_id, config))
);

Is it possible to achieve this behavior on MySQL?

Exprove
  • 1,301
  • 2
  • 18
  • 32
  • 1
    Check constraints cannot access other rows of the same let alone other tables. You'd need to write triggers on both tables that do the checks when schemas or configurations are inserted, updated or deleted. – sticky bit Dec 03 '21 at 11:14
  • @stickybit An user defined function (UDF) could solve this if MySQL allowed it? MariaDB allows UDFs on CHECK constraints, however I am not certain if a "select" query is allowed on UDFs. – Exprove Dec 03 '21 at 11:22
  • Hmm, would that fire, if the schema was changed or deleted effectively invalidating existing configurations? I have serious doubts here as the DBMS doesn't "see" the relation between the two and therefore "sees" no need to reevaluate the check constraints. – sticky bit Dec 03 '21 at 11:26
  • @stickybit Oh you are right, 'schema' column could change, leading to integrity problems. In that specific case I think the CHECK constraint is only validated on Insert/Update (table licenses), which indeed might cause problems. – Exprove Dec 03 '21 at 11:33
  • 1
    *An user defined function (UDF) could solve this if MySQL allowed it?* No. Referencing to another rows/tables is prohibited during the calculation at all, not in the expression itself only. – Akina Dec 03 '21 at 12:17

0 Answers0