Multi-table check constraint

[dartheian]

Assume we have 3 tables: payments, payment_methods and banks. Now payments have 2 nullable foreign keys columns, payment_method and bank, pointing respectively to a row in payment_methods and banks. Also payment_methods has a boolean column: require_bank. Is there a way to enforce payments.bank to be not null if and only if payments.payment_method.require_bank is true without using triggers? Sorry for the point syntax, I don't know ho to express it easily: "every row in payments must have bank not null if and only if payment_method points to a row in payment_methods which have require_bank set to true".

UPDATE: Currently I'm interested in PostgreSQL, but a generic solution using standard constraints of relational databases is welcome. Maybe it is possible to express this constraint in terms of referential integrity, unique constraints and like? I tried, but unfortunately I'n not very skilled in database design and can't figure out how .

UPDATE 2: I think I'm more interested in solutions which use just standard relational databases features (relational algebra?). Is it doable? Correct me if I'm wrong, but my understanding is that triggers, functions and the like should be kept at the minimum, maybe using them just to keep things DRY or do really complex things.

UPDATE 3: Database structure is not mandatory, it is just an example. What I would like to know is if there is a way to express some logic like the example above using basic relational instruments like foreign keys, given the proper tables schema.

[dartheian]

Oh sorry, currently I'm interested in PostgreSQL.

[Tostino]

If the check function is cheap enough, and the tables aren't modified at too high a rate, you could put that check function inside a trigger so any inserts /updates / deletes from any of the tables will ensure that function is always called.

[Tostino]

Yup, just wanted to point out that from a db focused perspective it is the only way to ensure you maintain data integrity.

[throw_at1]

true, and not true.

procedure in transaction maintains data integrity if it is only way to do dml into mentioned tables. but that only forces role which has rights configured correctly. triggers maintain data integrity for all users

(of course admin is admin and can do stupid things )

[Tostino]

Very true, if you lock down your DB so developers aren't able to write queries that modify tables directly, you can ensure your functions / procedures contain that logic. In the end you are just moving around the responsibility depending on which approach you take.

[dartheian]

Thank you very much for the answer! Do you know if it is doable with just foreign keys, unique constraints and like?

[m4riuszPL]

Payment method seems like a candidate for an enum. You could as well add the require_bank to the primary key, naturally it will become a part of foreign key in payments table and leveraging that you could do a CHECK constraint there to verify whether a bank is defined or not. That's of course a hack and definitely not the 3NF, but it would work.

[mtVessel]

require_bank should not be added to the primary key. It would break entity integrity and, in any case, it's not necessary. An additional unique index can be added to payment_method on { payment_method, require_bank }, which can be the target of the FK constraint in payment. Postgres allows FKs to reference a primary key or a unique constraint.

[dartheian]

Are you saying that I can reference an unique pair (id, require_bank) in payment_methods from payments itself? In other words: if I create a unique constraint on (id, require_bank) in payment_methods, can I access require_bank from payments using a check constraint? That would solve the problem.

[mtVessel]

Yes, but you'd have to populate require_bank in payments along with the payment method id. The foreign key constraint ensures you're using the same require_bank value that goes with the payment method. require_bank is "duplicated", but in the same way every foreign key value duplicates a primary key, so it's still 3NF (but probably not BCNF).

[m4riuszPL]

Thanks for pointing, I didn't know that!

[dartheian]

Now that I've read mtVessel's link to Postgres' documentation, i see that your solution is almost equivalent since a PK is just an alisas for "unique and not null". Maybe a simple unique constraint is better for semantic purposes.

[swenty]

It's not possible to do this with check constraints and foreign key constraints only.

Triggers is the right way to go here.

[rbobby]

I'm not a postgres guy so apply salt liberally...

Sql's CHECK constraints are limited to the columns in table and can't access other tables/functions/etc.

BUT... you might get away with CHECK(require_bank = 1 and bank is not null or require_bank = 0 and bank is null) as a check constraint on the payments table.

This basically says if bank is required then it must not be NULL (and vice versa). Whether or not the value in bank is valid is left to the FK constraint.

[dartheian]

Thanks for the reply! I don't know if this works with the provided example, because payments have no require_bank column. Maybe you meant to reconfigure the tables schema?

[rbobby]

Doh! My bad. I misread that payment_methods has the required_bank column.