Restrições de modelagem em agregados de subconjunto?

Como temos que abranger várias linhas, isso não podeCHECK ser implementado com uma restrição simples .

Também podemos descartar restrições de exclusão . Isso abrangeria várias linhas, mas apenas verificaria a desigualdade. Operações complexas como uma soma em várias linhas não são possíveis.

A ferramenta que parece melhor se adequar ao seu caso é CONSTRAINT TRIGGER(ou até mesmo simples TRIGGER- a única diferença na implementação atual é que você pode ajustar o tempo do gatilho com SET CONSTRAINTS.

Então essa é a sua opção 2 .

Uma vez que podemos contar com a restrição sendo aplicada o tempo todo, não precisamos mais verificar a tabela inteira. Verificar apenas as linhas inseridas na transação atual - ao final da transação - é suficiente. O desempenho deve ser ok.

Tambem como

Os dados contábeis são apenas anexos.

... só precisamos nos preocupar com as linhas recém- inserida . (Assumindo UPDATEou DELETEnão são possíveis.)

Eu uso a coluna do sistema xide comparo com a função txid_current()- que retorna o xidda transação atual. Para comparar os tipos, é necessário lançar ... Isso deve ser razoavelmente seguro. Considere esta resposta posterior relacionada com um método mais seguro:

• Como visualizar as tuplas alteradas em uma transação do PostgreSQL?

Demonstração

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 , por isso só é verificado no final da transação.

testes

INSERT INTO journal_line(amount) VALUES (1), (-1);

Funciona.

INSERT INTO journal_line(amount) VALUES (1);

Falha:

ERRO: Entradas não balanceadas!

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;

Funciona. :)

Se você precisar impor sua restrição antes do final da transação, poderá fazê-lo em qualquer ponto da transação, mesmo no início:

SET CONSTRAINTS insaft_check_balance IMMEDIATE;

Mais rápido com gatilho simples

Se você operar com várias linhas INSERT, é mais eficaz acionar por instrução - o que não é possível com acionadores de restrição :

Os gatilhos de restrição só podem ser especificados FOR EACH ROW.

Em vez disso, use um gatilho simples e atire FOR EACH STATEMENTpara...

• perder a opção de SET CONSTRAINTS.
• ganhar desempenho.

EXCLUIR possível

Em resposta ao seu comentário: Se DELETEfor possível, você pode adicionar um gatilho semelhante fazendo uma verificação de saldo de toda a tabela após um DELETE ter ocorrido. Isso seria muito mais caro, mas não importa muito, pois raramente acontece.

A solução do SQL Server a seguir usa apenas restrições. Estou usando abordagens semelhantes em vários lugares do meu sistema.

CREATE TABLE dbo.Lines
  (
    EntryID INT NOT NULL ,
    LineNumber SMALLINT NOT NULL ,
    CONSTRAINT PK_Lines PRIMARY KEY ( EntryID, LineNumber ) ,
    PreviousLineNumber SMALLINT NOT NULL ,
    CONSTRAINT UNQ_Lines UNIQUE ( EntryID, PreviousLineNumber ) ,
    CONSTRAINT CHK_Lines_PreviousLineNumber_Valid CHECK ( ( LineNumber > 0
            AND PreviousLineNumber = LineNumber - 1
          )
          OR ( LineNumber = 0 ) ) ,
    Amount INT NOT NULL ,
    RunningTotal INT NOT NULL ,
    CONSTRAINT UNQ_Lines_FkTarget UNIQUE ( EntryID, LineNumber, RunningTotal ) ,
    PreviousRunningTotal INT NOT NULL ,
    CONSTRAINT CHK_Lines_PreviousRunningTotal_Valid CHECK 
        ( PreviousRunningTotal + Amount = RunningTotal ) ,
    CONSTRAINT CHK_Lines_TotalAmount_Zero CHECK ( 
            ( LineNumber = 0
                AND PreviousRunningTotal = 0
              )
              OR ( LineNumber > 0 ) ),
    CONSTRAINT FK_Lines_PreviousLine 
        FOREIGN KEY ( EntryID, PreviousLineNumber, PreviousRunningTotal )
        REFERENCES dbo.Lines ( EntryID, LineNumber, RunningTotal )
  ) ;
GO

-- valid subset inserts
INSERT INTO dbo.Lines(EntryID ,
        LineNumber ,
        PreviousLineNumber ,
        Amount ,
        RunningTotal ,
        PreviousRunningTotal )
VALUES(1, 0, 2, 10, 10, 0),
(1, 1, 0, -5, 5, 10),
(1, 2, 1, -5, 0, 5);

-- invalid subset fails
INSERT INTO dbo.Lines(EntryID ,
        LineNumber ,
        PreviousLineNumber ,
        Amount ,
        RunningTotal ,
        PreviousRunningTotal )
VALUES(2, 0, 1, 10, 10, 5),
(2, 1, 0, -5, 5, 10) ;