1935797 - Replace CHECK and FOREIGN KEY constraints on `moz_bookmarks` with triggers

Status: RESOLVED FIXED

(Application Services :: Places, task)

Description

[Lina Butler (ex-Mozilla)]

We've seen a consistently high number of CHECK constraint failures on moz_bookmarks (bug 1876320). Unfortunately, those errors don't tell us which condition in the constraint failed—or which value in the new row would violate the condition, or which operation was responsible—so we don't have a good way to narrow down the cause of the problem.

Instead of using CHECK constraints, we can create BEFORE INSERT and BEFORE UPDATE triggers on moz_bookmarks to enforce the invariants that we want. Unlike a CHECK constraint, a trigger gives us access to all values in the new row, and lets us fail an insert or update with a custom error message.

(While we're at it, we can replace our FOREIGN KEY constraint on moz_bookmarks.parent with the same validation trigger—foreign keys are already implemented using triggers under the hood, and we can consolidate the validation into one BEFORE INSERT / UPDATE trigger).

Comment 1

[BMO Github Automation]

Attachment: [mozilla/application-services] Bug 1935797 - Replace CHECK and parent FOREIGN KEY constraints on `moz_bookmarks` with triggers. (#6518)

Comment 2

[BMO Github Automation]

Authored by https://github.com/linabutler
https://github.com/mozilla/application-services/commit/29b5fd0194236b4491a98d0992685b5110e27b02
[main] Bug 1935797 - Replace CHECK and parent FOREIGN KEY constraints on moz_bookmarks with triggers.

Authored by https://github.com/linabutler
https://github.com/mozilla/application-services/commit/5dfd0e68e59af88ca9e587f36a0c0f228c020274
[main] Bug 1935797 - Reject updates of moz_bookmarks.type.