Background: I'm using hsqldb for a project. I have a table of subscriptions, which I am retrofitting. I want to add the concept of active and inactive subscriptions. I want to enforce the constraint that active subscriptions must be unique, and inactive subscriptions may be non-unique (to enable a copy-edit-activate workflow)
Subscriptions table also includes "COLUMN id INTEGER GENERATED ALWAYS AS IDENTITY"
My brilliant, somewhat problematic plan is as follows: — create an "active" column, type boolean — create an "idUnlessActive" column, type integer — create a UNIQUE constraint including idUnlessActive, plus all the columns that actually define the subscription. — create two BEFORE triggers (INSERT and UPDATE): IF newRow.active = TRUE THEN SET newRow.idUnlessActive = NULL; ELSE SET newRow.idUnlessActive = newRow.id; END IF;
The fatal flaw that I've run into is that newRow.id isn't defined until the row is actually INSERTed. Because the BEFORE triggers fire before the INSERT, happens, I no longer have a UID to use, and the plan falls apart :o(
Any suggestions on (1) how to fix this so it does what I want, or (2) better plans on how to enforce the unique-unless-inactive constraint mentioned above?
It appears that hsqldb silently enforces the rule that AFTER triggers cannot alter the table. One thing I have tried is to create an AFTER trigger which causes a no-op UPDATE to the same row, and then I have a BEFORE UPDATE trigger which can successfully run my logic. That said, I have not yet figured out away to trigger an UPDATE after each INSERT on that table. This table is not very hot, so doing an extra UPDATE for every INSERT would be acceptable from a performance perspective.
Another alternative that comes to mind is just to keep active and inactive constraints in two separate tables. I would really like to avoid that, because then the subscription UID would change whenever it's "active" bit was flipped.