Hi Alex,
On 2024/05/03 12:49, Alex Hermann via discuss wrote:
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.
That would actually make sense.
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;
This is ~ what I understood. I can't find a way to determine the
{IS_INSERT} above, however (from
https://mariadb.com/kb/en/trigger-overview/):
"If a trigger contains an error and the engine is transactional, or it
is a BEFORE trigger, the trigger will not run, and will prevent the
original statement from running as well. If the engine is
non-transactional, and it is an AFTER trigger, the trigger will not run,
but the original statement will."
So under the assumption that you're using transactional tables here ...
is it possible to verify AFTER INSERT? In other words, instead of
testing if the statement will violate the constraints, rather check
afterwards if the constraints has been violated, and if so, roll back.
Depending on frequency of OK vs NOT OK updates the performance could be
horrendous overall though ... but if we assume that the bulk of queries
won't be problematic this should probably be OK.
Probably not helpful, so sorry if I'm just causing noise, I find this an
extremely interesting problem.
Kind regards,
Jaco
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
