Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
ghost-in-the-zsh provided a direct answer and I will try to offer a more general complementary one. One possible thing to try is a trigger that checks the integrity, but it cannot work for insert...
Answer
#1: Initial revision
by
Alexei
·
2020-11-10T21:30:31Z (about 4 years ago)
`ghost-in-the-zsh` provided a direct answer and I will try to offer a more general complementary one.
One possible thing to try is a trigger that checks the integrity, but it cannot work for inserts because the order must be something along the lines:
START TRANSACTION
INSERT INTO B
INSERT INTO A
INSERT INTO AxB
COMMIT
Since A's records must be checked, a trigger for A would make sense, but it would fire too early. A trigger on AxB would not prevent simple changes in A that do not connect to any B record.
For more complex validations one way is to ensure then from the application layer since it seems that the data is always changed by it. Integrity is ensured by **the proper use of transactions** and automatic testing.
As a safety net, a job might periodically check this integrity (this technique is used the data volume is big enough to make constraints checking quite expensive).