2

Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date and associated account).

Also, some transactions may be part of a transfer between different accounts and not a legitimate expense/income on its own (for instance a money cash out from your checking account), these should be linked together somehow. I could start modelling the application with the following schema:

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY
);

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  amount INT NOT NULL,
  date DATE NOT NULL,
  account INT NOT NULL REFERENCES accounts
);

CREATE TABLE transfers (
  outgoing_tx INT REFERENCES transactions,
  incoming_tx INT REFERENCES transactions,
  PRIMARY KEY (outgoing_tx, incoming_tx)
);

Now comes the question part. When I find myself modelling a domain like this I tend to heavily rely on CHECK clauses to further constrain the schema to the domain and ban unwanted values/states at runtime.

For instance, in this example I wouldn't care for empty transactions:

ALTER TABLE transactions
ADD CONSTRAINT no_pointless_txs CHECK (amount <> 0);

However, when it comes to rules that involve more than a single nice little row I always feel like I need to write too much boilerplate code. For transfers you would want to check that the transactions they point to belong to different accounts, are from the same day and have the same amount, except outgoing_tx must be negative and incoming_tx positive.

This is more or less what I would like to write, i.e. I'd like to navigate through foreign keys to access other table columns by doing select/joins under the hood:

ALTER TABLE transfers
ADD CONSTRAINT no_same_accounts
  CHECK (outgoing_tx->account->id <> incoming_tx->account->id),

ADD CONSTRAINT no_different_dates
  CHECK (outgoing_tx->date = incoming_tx->date),

ADD CONSTRAINT correct_balance
  CHECK (outgoing_tx->amount < 0 AND 0 = outgoing_tx->amount + incoming_tx->amount);

Instead, every time I want to enforce rules like these I have no option but to write lenghty boilerplate PL/SQL functions such as get_date(int) or get_account_id(int) to do the queries manually.

CREATE OR REPLACE FUNCTION get_account_id(tx_id int) RETURNS int AS $$
  SELECT account FROM transactions WHERE id = $1;
$$ LANGUAGE SQL;

-- Every other function...

ALTER TABLE transfers
ADD CONSTRAINT no_same_accounts
  CHECK (get_account_id(outgoing_tx) <> get_account_id(incoming_tx));

-- Every other constraint using its corresponding function...

So I guess I just want to know if there are easier, more mantainable ways to go about this kind of use case, and OCL-style navigation came to my mind.

  • 1
    One might choose to enforce this through the use of an "instead of" trigger. – Max Vernon Nov 21 '15 at 15:29
  • I'd not use constraint for implementing complex business rules . My approach in such cases is to create one procedure that validates everything and inserts a row into transaction table , and restrict all users from direct write access to the table, so they must use the procedure in order to insert data. Another, even better approach, is to let application developers enforce everything that belongs to business domain . – a1ex07 Nov 21 '15 at 17:25
2

It appears you are looking for a SQL database that implements the SQL Standard CREATE ASSERTION statement. AFAIK there is no mainstream SQL database that actually supports this.

Therefore, the more complex data integrity rules that you wish to implement must be done so programmatically. This can be done using triggers in the DBMS or procedurally either using stored procedures in the database or within the external application.

However it is implemented, careful consideration needs to be made to ensure the ACID properties are followed to ensure the reliability of the database transactions. In particular this may mean explicit locking mechanisms are employed wherever the constraints are validated.

There is generally also the requirement to ensure that the constraints are enforced as efficiently as possible.

For an in-depth examination of the implementation of complex integrity constraints using DBMS triggers I would suggest reading Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars.

  • Thank you! I had never heard about that statement, but I believe that's exactly what I was longing for. Its kind of weird that no vendor ever bothered to implement it (being in the SQL-92 standard and all), isn't it? Oh, well... – Marcel Hernandez Nov 27 '15 at 18:53
1

I use before triggers for complex rules. This requires the ability to throw an exception from within the trigger for records that do not meet the conditions. Normally you only need to run these triggers before inserts and updates.

There can be issues if the data you are validating against is being modified with the transaction.

I generally use CHECK constraints for simple rules where a column has a limited range of permissible values not covered by a foreign key constraint.

Even if these rules are being applied in the code, it can be useful to have them in the database. This can prevent ad-hoc updates from breaking data integrity as well as catch errors in the application. (Verify the constraint as well as the code as either could be wrong.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.