On vrijdag 3 mei 2024 12:27:49 CEST Jaco Kroon via discuss wrote:
May I assume that it's also not quite as trivial as doing something like this:
CONSTRAINT `valid_phone` CHECK (`phoneno` regexp '^0[1-8][0-9]{8}$')
Correct. It does its own conflict detection as UNIQUE keys can't represent it.
I wasn't thinking that the CONSTRAINT needs to be UNIQUE, the above is a CHECK ... which could potentially use a stored function to perform the check ...
IIRC with MariaDB, it is not possible to query the table within a CHECK CONSTRAINT. What the trigger should do in pseudocode: IF EXISTS (SELECT * FROM {horrifying conditions} ) THEN IF {IS_INSERT} OR {further validation for REPLACE} THEN SIGNAL SQLSTATE '45000' MESSAGE_TEXT = 'Row is invalid...'; END IF; END IF; -- Alex Hermann