As we have to span multiple rows it cannot be implemented with a simple CHECK
constraint.
We can also rule out exclusion constraints. Those would span multiple rows, but only check for inequality. Complex operations like a sum over multiple rows are not possible.
The tool that seems to best fit your case is a CONSTRAINT TRIGGER
(Or even just a plain TRIGGER
- the only difference in the current implementation is that you can adjust the timing of the trigger with SET CONSTRAINTS
.
So that's your option 2.
Once we can rely on the constraint being enforced at all times, we need not check the whole table any more. Checking only rows inserted in the current transaction - at the end of the transaction - is sufficient. Performance should be ok.
Also, as
The accounting data is append-only.
... we only need to care about newly inserted rows. (Assuming UPDATE
or DELETE
are not possible.)
I use the system column xid
and compare it to the function txid_current()
- which returns the xid
of the current transaction.
To compare the types, casting is needed ...
This should be reasonably safe. Consider this related, later answer with a safer method:
Demo
CREATE TABLE journal_line(amount int); -- simplistic table for demo
CREATE OR REPLACE FUNCTION trg_insaft_check_balance()
RETURNS trigger AS
$func$
BEGIN
IF sum(amount) <> 0
FROM journal_line
WHERE xmin::text::bigint = txid_current() -- consider link above
THEN
RAISE EXCEPTION 'Entries not balanced!';
END IF;
RETURN NULL; -- RETURN value of AFTER trigger is ignored anyway
END;
$func$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER insaft_check_balance
AFTER INSERT ON journal_line
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE trg_insaft_check_balance();
Deferred, so it is only checked at the end of the transaction.
Tests
INSERT INTO journal_line(amount) VALUES (1), (-1);
Works.
INSERT INTO journal_line(amount) VALUES (1);
Fails:
ERROR: Entries not balanced!
BEGIN;
INSERT INTO journal_line(amount) VALUES (7), (-5);
-- do other stuff
SELECT * FROM journal_line;
INSERT INTO journal_line(amount) VALUES (-2);
-- INSERT INTO journal_line(amount) VALUES (-1); -- make it fail
COMMIT;
Works. :)
If you need to enforce your constraint before the end of the transaction, you can do so at any point in the transaction, even at the start:
SET CONSTRAINTS insaft_check_balance IMMEDIATE;
Faster with plain trigger
If you operate with multi-row INSERT
it is more effective to trigger per statement - which is not possible with constraint triggers:
Constraint triggers can only be specified FOR EACH ROW
.
Use a plain trigger instead and fire FOR EACH STATEMENT
to ...
- lose the option of
SET CONSTRAINTS
.
- gain performance.
DELETE possible
In reply to your comment: If DELETE
is possible you might add similar trigger doing a whole-table balance check after a DELETE has happened. This would be much more expensive, but won't matter much as it rarely happens.
It ensures that the constraint is enabled after it is created. Your ALTER TABLE
statement includes WITH NOCHECK
which is the piece that says not to check for existing bad data during the creation of the constraint.
As written, the existing data will not be checked against the constraint because of the WITH NOCHECK
in the first statement. Issuing the second statement will enable the check against the constraint for any future changes to the table that are covered by the constraint, up to the point that an ALTER TABLE [dbo].[T2] NOCHECK CONSTRAINT [FK_T2_T1]
is issued.
The statements, as written, are basically saying "Create this foreign key constraint but don't check it against existing data. Make it active for any upcoming changes to the data."
Best Answer
It seems the database and column collations are case-insensitive so the
LIKE
expression is also case-insensitive.One way to perform a case-sensitive compare in this scenario is by adding a
COLLATE
clause, specifying a case-sensitive collation. For example, if your database default collation is a case insensitive collation such as Latin1_General_CI_AS, the example below will override that collation with the case-sensitive version of the collation for the literal and perform the case-sensitive comparison you want:Below are related collation documentation pages for your perusal: